Returning a single cell value from a separate sheet

New Contributor



I need to return a value from a specific cell on a different sheet. I have found that I need to use the INDIRECT formula as I need to return these values from several sheets and don't want to change the sheet name every time.

However, I am not sure what formula I can use to return the value of this cell. I have tried using INDEX but keep getting the #REF error.


INDIRECT("Week " & ROW(A101) & "!E$12")

I have added here the INDIRECT part of the formula, I just need to know how I can return the value in the cell.

4 Replies
best response confirmed by lucyhg (New Contributor)


Since the name of the worksheet contains a space, you must enclose it in single quotes:

=INDIRECT("'Week " & ROW(A101) & "'!E12")

This will return the value of cell E12 on the sheet named Week 101

This is still returning the #REF! error


That means that there is no worksheet named Week 101

Its working now thank you