The usual way to reference a sheet is here but how do you reference a sheet when the sheet's name is a value in a cell?

For example,

A1 = "Sheet number two"
B1 = A1&"!B4"

Here I want B1 to display the value in 'Sheet number two'!B4 but it's not working for me. Any ideas?

2 Answers 2


You need to use the indirect() formula. So in cell B1 you need to put this formula:

  • A problem with this is if you copy B1 to C1, it won't check B5 in referred sheet as expected. Because B4 is a string... any remedy?
    – Jonny
    Feb 9, 2015 at 4:07

I believe the values need to be outside the speechmarks otherwise it doesn’t reference the other sheet.

  • Typographic quotes doesn't work in formulas, only straight quotes, beside that the proposed formula was already in the previous answer that was posted 9 years ago! Aug 11, 2021 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.