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

• Here we shall try to make use of the +Sheet(X)!(XX) formula.
• Select the cell in which you want to swap the data.

• In the formula bar type this command:

  +Sheet(X)!(XY)

• X stands for sheet label

• XY stands for the targeted cell coordinates.
• Don't take brackets into account.

E.g: +Sheet2!B3 : will copy data from cell B21 of sheet 2.

Answer 2

=INDIRECT(A2&"!A1") and if A2 is the previous or other sheet name OR number, it should work. Note: in a copy/paste of this formula, the quote marks (") may become dual apostrophe marks (') - this won't work. If the sheet is then cloned, simply change A2 to be the now previous sheet name. No need to edit the formula itself. I have two workbooks now using this formula. One already had a cell with the previous sheet name used for calculations on that sheet. The sheets can be actual names (Jan, Feb, etc.) or numbers (352, 353, etc.).

Answer 3

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).