Google Sheets, formula, reference to variable sheets

Question

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?

Answer 1

Use INDIRECT

Let say that A1 has the sheet name and that we want to reference A1 of that sheet. The formula will be:

=INDIRECT(A1&"!A1")

Related Q&A

• Selecting a sequence of sheets in Google Docs / Spreadsheets
• Dynamically Building Array Of Sheet Names From A Range Of Cells

Answer 2

the answer is yes:

• cell B1: =INDIRECT(A1 & "!$AC$1")
• cell B2: =INDIRECT(A2 & "!$AC$1")