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?
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.
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 ' .
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With