Use string value from a cell to access worksheet o

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

This will only work to column Z, but you can drag this horizontally and vertically.

=INDIRECT("'"&$D$2&"'!"&CHAR((COLUMN()+64))&ROW())

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

INDIRECT is the function you want to use. Like so:

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

With INDIRECT you can build your formula as a text string.

Answer 6

You need INDIRECT function:

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