[Solved] Formula to reference data in another spreadsheet

[tperry]

This may have been addressed before, but I could not find anything that made sense to me (I have been a long time excel user). I am new to open office and I have an interest in trying to work with it,. Mainly because my employer is using it.

So, I work at a cab company and have created a spreadsheet to help track my daily drops and such.
What I want to do now is create a separate spreadsheet to summarize and calculate what I have earned each week. Yes, I can just manually copy and edit each reference, but I'd like to be able to create a formula that can access the spreadsheet. All I'd have to do is just have the name of the sheet in a cell, have the formula reference it and pull up the data.

[Lupp]

Assuming you are working on Sheet2 and want to reference the content of cell B7 of Sheet1, this is done by

Code: Select all

Sheet1.A7

To get the same reference taking the sheetname 'Sheet1' from cell A1 where it was entered as text, you can use

Code: Select all

INDIRECT(A1&"A7")

If you want to reference the cell in the 12th column of the 5th row of the sheet whose name was placed into cell A1 of the current sheet, you can use

Code: Select all

OFFSET(INDIRECT(A1&"A1");5-1;12-1)

and so on...

To advise more precisely I would need a more precise explanation about what you try to achieve.
As you described yourself as a former Excel user you may explain your goals by giving the formulas you would expect to work in Excel delivering the results you want.

[robleyd]

It may be that a pivot table aka data pilot will group/sum the information you need.

[tperry]

What I am trying to do is pull data from a different spreadsheet.
On my Summary sheet, I pull 8 pieces of data. Sales, Credit Card, Tips, etc...
But each day is on a separate spreadsheet. They are named in the following format 3 03 17 tom.ods. They are not tabs in the same spreadsheet, but completely different spreadsheets. I am going to go back and look at what the responses are, but they do not seem correct. What I want to do is put a date in, have the spreadsheet pull the date out and convert it to the format I need, then pull the data.
indirect is what I think I need, but I can't seem to get it to access the spreadsheet.

I try to avoid using pivot tables. Will use them, but I have found that most of the time, I can't do exactly what I want in them.

Can I upload the spreadsheet?

[RoryOF]

It may be that OO will not like the spaces in the names. If that turns out to be the case, I suggest replacing these spaces with underscores.

[tperry]

Removed spaces and still not having any luck

I can manually do the reference:
'file:///C:/Users/Tom/Documents/MarshallTaxiFiles/30317tom.ods'#$Drop_Calculator.$A$3

But, when I try using index, indirect I keep getting ERR:502. Format I am trying is like this:
Cell A1 contains "file:///C:/Users/Tom/Documents/MarshallTaxiFiles/" (Without quotes)
Cell b1 contains "30317tom.ods" (Without quotes) This would change for different files
Cell c1 contains "#$Drop_Calculator" (Without quotes) This would change for different Cell References

Tried using Indirect(a1&b1&c1;a3) and I get ERR:502, but I can't find WHAT is the invalid reference
Same thing when I try index.

[MrProgrammer]

Hi, and welcome to the forum.

I can manually do the reference:
'file:///C:/Users/Tom/Documents/MarshallTaxiFiles/30317tom.ods'#$Drop_Calculator.$A$3
But, when I try using index, indirect I keep getting ERR:502. Format I am trying is like this:
Cell A1 contains "file:///C:/Users/Tom/Documents/MarshallTaxiFiles/" (Without quotes)
Cell b1 contains "30317tom.ods" (Without quotes) This would change for different files
Cell c1 contains "#$Drop_Calculator" (Without quotes) This would change for different Cell References
Tried using Indirect(a1&b1&c1;a3) and I get ERR:502, but I can't find WHAT is the invalid reference

Read the documentation on INDIRECT, Help → Index → INDIRECT. The first argument is required; the second is optional and only needed if you want to use R1C1 references, which you don't. Given the contents of those cells use =INDIRECT("'"&A1&B1&"'"&C1&".A3") to reference

'file:///C:/Users/Tom/Documents/MarshallTaxiFiles/30317tom.ods'#$Drop_Calculator.$A$3

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[tperry]

I will swear on a stack of bibles, that is the way I was trying and for some reason, it kept failing. Now it works.

Thank you!

[RusselB]

There are differences in punctuation between what you said you were trying to use

Code: Select all

=Indirect(a1&b1&c1;a3)

and MrProgrammers solution

Code: Select all

=INDIRECT("'"&A1&B1&"'"&C1&".A3")

The various quotation marks (both single and double) as well as the proper character separating the file name and the cell address are, specifically, the differences.

[tperry]

Figured it might have been punctuation. Always gets me.