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!


2 Replies
best response confirmed by kczyzyk (New Contributor)


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




This can be filled to the right.

That's perfect, thank you!