excel - Use string value from a cell to access worksheet of same name

Question

I have 2 worksheets: Summary and SERVER-ONE.

In cell A5 on the Summary worksheet, I have added the value SERVER-ONE.

Next to it, in cell B5, I would like a formula that uses the value in A5 to display the value of G7 in the worksheet of the same name (SERVER-ONE).

I could manually use:

='SERVER-ONE'!G7

However I would like this to be dynamic, so I can easily add more worksheets.

I tried the obvious with no joy:

='A5'!G7

Any suggestions?

Answer 1

You can use the formula INDIRECT().

This basically takes a string and treats it as a reference. In your case, you would use:

=INDIRECT("'"&A5&"'!G7")

The double quotes are to show that what's inside are strings, and only A5 here is a reference.