How to use a sheet name that is referenced in a cell in a formula?

Question

Column B is a list of my sheet names created using Index. I simply want the formula in column C to go to a certain cell on whatever sheet name is listed in column B. I’m struggling with what I think is very simple? I have a formula that works, but it doesn’t allow me to change the name of the sheet. I thought it would be something like ='B4'!$P$33

My formula thus far.

microsoft excel

Gary's Student · Answer

Say we wanted:
=Sheet1!Z100

but wanted to put the sheetname in a separate cell.  Use:
=INDIRECT("'" & B4 & "'!Z100")

Please note the single quotes!

FlexYourData · Answer

Use the INDIRECT function to return the reference specified by a string.
You just concatenate your string and wrap it with the function.
=INDIRECT(B4&"!$P$33")

How to use a sheet name that is referenced in a cell in a formula?

2 Answers

Add your own answers!

Ask a Question