SOLVED

Reference a cell to grab the sheet name for another cell reference?

New Contributor

I want to use a cell in the current workbook to reference a cell in another workbook.  Essentially I have a lot of copies of the same formatted data and I'm trying to compile a summary. Is there a way to reference a cell instead of typing in something like "=SheetName!$L11", "SheetName" would be found from the value in another cell (ie see screenshot where Reach1 would be the sheet name).  I'm not sure how exactly to ask this question.  I'm sure I could do it in VBA but ideally want to keep it not macro-enabled. Thanks in advance!

 2021-11-16_14-24-31.jpg

2 Replies
best response confirmed by kczyzyk (New Contributor)
Solution

@kczyzyk 

If you mean that you want to reference a cell in another sheet in the same workbook, you can use the INDIRECT function:

 

=INDIRECT("'"&H2&"'!L11")

 

This can be filled to the right.

That's perfect, thank you!