If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:
To get the value in Sheet2!B2 instead, you can add 1 to
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.