Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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:… – 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
up vote 5 down vote accepted

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


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
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 '14 at 12:14

You must log in to answer this question.

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