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

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

User · Accepted Answer

You can use the formula INDIRECT     This basically takes a string and treats it as a reference  In your case  you would use    INDIRECT     amp A5 amp    G7     The double quotes are to show that what s inside are strings  and only A5 here is a reference

User · Answer

not sure if you solved your question  but I found this worked to increment the row number upon dragging      INDIRECT     amp  A 5 amp     G  amp 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

User · Answer

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     amp D 2 amp     B  amp ROW      My layout has sheet names as column headers  B2  C2  D2  etc   and maps multiple row values from Column B in each sheet

User · Answer

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     amp  A 5 amp    R  amp ROW   amp  C  amp 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

User · Answer

INDIRECT is the function you want to use  Like so    INDIRECT     amp A5 amp    G7     With INDIRECT you can build your formula as a text string

User · Answer

You need INDIRECT function    INDIRECT     amp A5 amp    G7

User · Answer

This will only work to column Z  but you can drag this horizontally and vertically    INDIRECT     amp  D 2 amp      amp CHAR  COLUMN   64   amp ROW

User · Answer

Guess  user3010492 tested it but I used this with fixed cell A5 --   A 5 and fixed element of G7 --   G7   INDIRECT     amp  A 5 amp     G7     Also works nested nicely in other formula if you enclose it in brackets

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

Examples related to excel

Examples related to worksheet-function