Refer to Sheet with value in Cell?

Question

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?

Jacob Jan · Accepted Answer · 2015-02-05 20:21:39Z

19

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

=indirect(A1&"!B4")

Jacob Jan

23.3k16 gold badges106 silver badges195 bronze badges

answered Mar 6, 2012 at 3:48

mhoran_psprep

7,6831 gold badge32 silver badges34 bronze badges

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
Commented Feb 9, 2015 at 4:07

Add a comment |

matjkd · Accepted Answer · 2021-08-09 11:51:56Z

-1

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

=indirect(A1&”!”&B4)

answered Aug 9, 2021 at 11:51

matjkd

1

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!
– Rubén - Volunteer Moderator - ♦
Commented Aug 11, 2021 at 3:15

Add a comment |

2 Answers 2