How to reference a cell in sheet using the sheetname written in cell

0
votes

I have "Week 1", "Week 2", "Week 3".... as my headers in cells A1, B1, C1....

I also have "Week 1", "Week 2", "Week 3".... sheet names.

In the second row, I would like to reference to corresponding sheets' cells. For example in cell A2, I gives have tried indirect function INDIRECT(A1) but it gives #ref error. I actually want to reference sheet Week 1, but all I can imagine is this function =SHEETS(INDIRECT(A1)). Of course I have the same error again.

I want to reference to sheet "Week 1" cell A2, by using current sheet's A1 value ("Week 1") in the formula.

Any help is appreciated.

2
excelreferenceexcel-indirect

2 Answers

1
votes

You need to put the sheet name in brackets in the INDIRECT.

=INDIRECT(A1&"!A2")

For a different workbook, you use brackets:

=INDIRECT("["&A1&"]A2")

So you can combine them:

=INDIRECT("["&A1&"]"&A2&"!B1")
0
votes

You use the function incorrectly. The proper usage is =INDIRECT("A1") by using "".

As for your questions, to be able to reference a sheet name by using cell value, you need to use INDIRECT and ADDRESS function, not SHEET function. Sheet function returns the number of sheets.

The proper function you are looking for is =INDIRECT(ADDRESS(2,1,,,A1))