excel link to another sheet in same workbook

Question

Suppose I have an excel workbook having four sheets labeled: 352 353 354 Sheet1

Suppose that the A1 cell in sheet 352 is "this is taken from 352"

Suppose that the A1 cell in sheet 353 is "this is taken from 353"

Suppose that the A1 cell in sheet 354 is "this is taken from 354"

Now I would like to be able to link back to the various A1 cells, so I'm asking about a link from one sheet (e.g., the one labeled 352) to another, namely the sheet labeled Sheet1.

I know how to normally do this: in Sheet1 enter the formula ='352'!$A$1

However, I want entries in Sheet1 to be used as reference names instead of explicitly using 352. So, for example, if the entry of A1 in Sheet1 is 352, then I want the formula I am looking for to give me the same exact value as what ='352'!$A$1 would give.

To elaborate a bit, A1 in Sheet1 would be 352 A2 in Sheet1 would be 353 A3 in Sheet1 would be 354.

What I want intuitively is basically ='[Sheet1!$B$1]352'!$A$1 but I still have that pesky 352 there but I don't want 352 there, I want a dynamic reference to A1 in Sheet1 (which just so happens to be 352 at the moment).

Answer 1

You can use this formula (suppose in your Sheet1 in cell B1 stores correct sheet name):

=INDIRECT("'" & Sheet1!$B$1 & "'!" & CELL("address",A1))

you can also use

=INDIRECT("'" & Sheet1!$B$1 & "'!A1")

but first approach is better, because if you'd like to drag formula down in first approach you will have "relative reference" A1, that will change accordingly to the row, that stores formula, but in second approach you will have the same formula for all rows (it will always refers to A1 cell).