microsoft excel - Use worksheet name as a variable in a lookup

I have 2 workbooks with 60 plus worksheets. I need to copy data from one workbook to the other but would like to capture and use the worksheet name within the cell. This is because when I create the workbook I make a master worksheet and then copy it 60 plus times. The above would make the cell ref for each worksheet the corresponding worksheet cell in the other file.

Answer

The Question

I’m guessing that you have two workbooks (i.e., Microsoft Excel .xls spreadsheet files); let’s call them Master.xls and Book2.xls.  And [Master.xls]Sheet1 contains

              A                       B                       C
1   =[Book2.xls]Sheet1!A1   =[Book2.xls]Sheet1!B1   =[Book2.xls]Sheet1!C1   ...
2   =[Book2.xls]Sheet1!A2   =[Book2.xls]Sheet1!B2   =[Book2.xls]Sheet1!C2   ...

and the problem is that, when you create [Master.xls]Sheet2 as a copy of [Master.xls]Sheet1, it also gets

              A                       B                       C
1   =[Book2.xls]Sheet1!A1   =[Book2.xls]Sheet1!B1   =[Book2.xls]Sheet1!C1   ...
2   =[Book2.xls]Sheet1!A2   =[Book2.xls]Sheet1!B2   =[Book2.xls]Sheet1!C2   ...

but you want the cells in [Master.xls]Sheet2 to be referencing [Book2.xls]Sheet2 rather than [Book2.xls]Sheet1.

The Answer

Establish a “helper cell”; let’s say Z1 – somewhere that’s out of the way.  (You don’t really need to have this “helper cell”, but things will be much messier if you don’t.)  Fill it with a formula that evaluates to the name of the current sheet, as documented in Is it possible to get the sheetname?, such as

=MID(CELL("filename"), FIND("]",CELL("filename"))+1, 256)

Then set the cells in Master.xls to

=INDIRECT("[Book2.xls]" & $Z$1 & "!R" & ROW() & "C" & COLUMN(),  FALSE)

Explanation

[Master.xls]Sheet42!Z1 evaluates to Sheet42, so, in every cell in [Master.xls]Sheet42, the first two terms of the first argument to INDIRECT will evaluate to [Book2.xls]Sheet42.  And, in cell S17 (for example) of any sheet, ROW() will evaluate to 17 and COLUMN() will evaluate to 19, and so the last four terms add up to !R17C19.  Put it all together and you’ve got

=INDIRECT("[Book2.xls]Sheet42!R17C19",  FALSE)

When the second argument to INDIRECT is FALSE, it interprets strings like R17C19 to mean S17; so this is equivalent to

=INDIRECT("[Book2.xls]Sheet42!S17")

which is equivalent to

=[Book2.xls]Sheet42!S17