If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()))
To get the value in Sheet2!B2 instead, you can add 1 to COLUMN()
:
=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()+1))
Explanation:
INDIRECT
returns the value stored in a cell using some text formatted as a standard cell reference such as "A2", or "Sheet2!$A$2". Now we will try to generate that text automatically.
ADDRESS(row, col)
converts cell and column numbers to a standard cell reference in text format. ADDRESS(1,1) returns "$A$1", and so on.
ROW()
returns the number of the row the formula sits in, and COLUMN()
for the column number. Since the formula is in A2, ROW()
will equal 2 and COLUMN()
will equal 1. These are just plain numbers, so we can add 1 to the column number to get B instead of A. We give those numbers to ADDRESS(2,2)
to get "$B$2".
- We append this address to the sheet name we want that is in B2:
B2&!"&ADDRESS(ROW(),COLUMN())
, not forgetting the exclamation mark between, to obtain the full cell reference we need: "Sheet2!$B$2"
- Last step, we provide that text to
INDIRECT
, which will go and fetch the value at that address.