Google says in their web help how to reference data from another sheet, but what happens if what I need is to make the reference depending on my fields on the current sheet?
Let's say I have a sheet named 'Sheet1' and a sheet named 'Sheet2'. And on my current sheet (results) I have a column where I'll place the name of each sheet:
On Cell A1 value is: Sheet1
On Cell A2 value is: Sheet2
and so.
I want to have Cell B1 to get some data from the sheet quoted in A1 (i.e.Sheet1): =Sheet1!$AC$1
I want to have Cell B2 to get some data from the sheet quoted in A2 (i.e.Sheet2): =Sheet2!$AC$1
…and so on (for that, I need to copy down the formula with reference to column A content, to say, the corresponding sheet)
In summary: the formula should include reference to a VARIABLE sheet.
I've tried with both =concatenate() and also with =indirect() but no success…
Is it possible?
Best Answer
Use INDIRECT
Let say that A1 has the sheet name and that we want to reference A1 of that sheet. The formula will be:
Related Q&A