Dynamic variables in Excel formulas-CodePudding

I am trying to make some dynamic formulas on Excel.

Problem is as follows;

I have a list of names on a row; i.e. A, B, C, D.
I also have the tabs with names like tab-A, tab-B, tab-C etc...

On next column to the names, I want to introduce some specific cell value from that tab - example below.

    ColA  ColB
    ----  ----
      A    ='tab-A'!G9 ==> G9 cell of the tab-A
      B    ='tab-B'!G9 ==> G9 cell of the tab-B
      C    ='tab-C'!G9 ==> ...
      D    =...

The question is, how to generate dynamic formulas that contains values from other cells: 'tab-A'!G9.

CodePudding user response：

Use the function INDIRECT.

=INDIRECT("'tab-" & A1 & "'!G9")

CodePudding user response：

Names get created by default, referring to their worksheet. It is possible to create a name which is only applicable to one worksheet, but this needs to be done in the name manager, as you can see in following example:

When I open Sheet1:

When I open Sheet2:

I have created name "test", just by entering the name in the name box, and automatically the name is usable in the whole workbook. The other name "tralala", however, is only applicable for "Sheet1" and is not usable in "Sheet2", hence the #NAME? error.