Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 911
KingBob Avatar asked Jun 03 '13 14:06

KingBob


People also ask

How do you reference a worksheet based on cell value?

To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address. For example, to refer to cell A1 in Sheet2, you type Sheet2! A1.

Can I reference a sheet name in an Excel formula?

Notes: The worksheet name comes before the cell address, followed by an exclamation mark ! . If the worksheet name includes spaces, enclose it in single quotation marks ' .

How do you link a sheet name to a cell?

Go to the cell which you want to reference the current sheet tab name, please enter =TabName() and then press the Enter key. Then the current sheet tab name will be display in the cell.


1 Answers

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.

like image 66
Jerry Avatar answered Sep 20 '22 15:09

Jerry