Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to reference a cell in another sheet, and the formula I get when pressing = followed by changing sheet, clicking on the cell and pressing Enter, gives me

'Sheet2'.A2

Which is fine, except that cell B3 in Sheet1 contains the name of the sheet to select from.

So how can I avoid hard coding the name of the sheet and instead use the value of a cell as the sheet name?

I've googled and looked up OOo documentation and wikis, but to no avail.

share|improve this question
    
possible duplicate: superuser.com/questions/371970/… –  tohuwawohu Jul 12 '12 at 4:44
    
Not a duplicate, but related in the use of INDIRECT for the solution. But this question is specific to referencing another sheet, for which special syntax seems to be needed. –  jgivoni Jul 12 '12 at 9:33
add comment

1 Answer

up vote 2 down vote accepted

What you're looking for is called an Indirect Reference.

INDIRECT(B3&".A2")

I believe would do what you're looking for.

To explain, it's the concatenation of the string value of cell B3 in the current sheet and the string ".A2". The resulting string, "Sheet2.A2", is then evaluated as a reference.

share|improve this answer
    
Thanks, that's what I needed! Here's the formula that worked in the end for my example: INDIRECT(B3&".A2") - where B3 is the cell in the current sheet that contains the name of the sheet to look for A2, which has the value I'm looking for. –  jgivoni Jul 12 '12 at 9:30
    
That's good. In that case you should upvote and accept this answer. –  zebediah49 Jul 12 '12 at 18:48
    
Yeah, I just edited the answer first so other people don't have to look in the comments for the solution :-) –  jgivoni Jul 13 '12 at 6:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.