6

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.

Improve this question
2
  • possible duplicate: superuser.com/questions/371970/…
    – tohuwawohu
    Jul 12, 2012 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, 2012 at 9:33

1 Answer 1

Reset to default
8

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.

Improve this answer
3
  • 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, 2012 at 9:30
  • Yeah, I just edited the answer first so other people don't have to look in the comments for the solution :-)
    – jgivoni
    Jul 13, 2012 at 6:56
  • Be careful, In some Libre Office versions the formula might a bit difference. For example the following formula works in mine. INDIRECT(B3&"!A2") instead of INDIRECT(B3&".A2")
    – user352602
    Aug 2, 2014 at 12:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .