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

excel worksheet-function
KingBob picture KingBob · Jun 3, 2013 · Viewed 190.7k times · Source

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

Jerry picture Jerry · Jun 3, 2013

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.

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More