'Dynamic variables in Excel formulas

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.

excel

Solution 1:^[1]

Use the function INDIRECT.

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

Solution 2:^[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Andreas
Solution 2	Dominique

'Dynamic variables in Excel formulas

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]