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?

Solution 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.

Solution 2:

You need INDIRECT function:

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

Solution 3:

not sure if you solved your question, but I found this worked to increment the row number upon dragging.

= INDIRECT("'"&$A$5&"'!$G"&7+B1)

Where B1 refers to an index number, starting at 0.

So if you copy-drag both the index cell and the cell with the indirect formula, you'll increment the indirect. You could probably create a more elegant counter with the Index function too.

Hope this helps.

Solution 4:

Here is a solution using INDIRECT, which if you drag the formula, it will pick up different cells from the target sheet accordingly. It uses R1C1 notation and is not limited to working only on columns A-Z.

=INDIRECT("'"&$A$5&"'!R"&ROW()&"C"&COLUMN(),FALSE)

This version picks up the value from the target cell corresponding to the cell where the formula is placed. For example, if you place the formula in 'Summary'!B5 then it will pick up the value from 'SERVER-ONE'!B5, not 'SERVER-ONE'!G7 as specified in the original question. But you could easily add in offsets to the row and column to achieve the desired mapping in any case.

Solution 5:

By using the ROW() function I can drag this formula vertically. It can also be dragged horizontally since there is no $ before the D.

= INDIRECT("'"&D$2&"'!$B"&ROW())

My layout has sheet names as column headers (B2, C2, D2, etc.) and maps multiple row values from Column B in each sheet.