Pull defined name from text field

[random360]

I have a workbook where I need to create many (30+) tabs all of which are identical except for one thing. Each tab has about 100 functions each of which pull from a set of defined names. The names used on each tab will be different. Rather than updating all of my formulas on all of my tabs I would like to be able to simply write in the defined name into a cell and have all of the functions look at this cell and recognize the contents of the cell as a defined name and use it accordingly. That way I can simply update a few cells on each tab for the defined names and be good to go.

as an example:

=VLOOKUP(I3,WBH,2,TRUE)

WBH is a defined name, and appears several times on the same tab. However, when I copy this tab to make the next one, I need to update all instances of WBH to WBM. I would like to replace WBH in this function with a reference to a cell where I write in WBH, and have excel recognize that I mean the defined name, and not simply text (I suppose I could do a find and replace, but that is more work, and also runs the risk of my failing to update one of the terms on one of the many tabs I have to do).

Is there a way to do this? If not, does anyone have suggestions for workarounds or better ways to approach this problem?

[WideBoyDixon]

Well, you *could* use INDIRECT and that would work but it might run very slowly. E.g.

Please Login or Register  to view this content.

Then you could put "WBH" or "WBM" in Z1 and it should work.

WBD

[6StringJazzer]

The names used on each tab will be different.

Why do all the names have to be different? Why not use the same names on each tab, and set the scope to Worksheet instead of Workbook?

[random360]

Well, you *could* use INDIRECT and that would work but it might run very slowly. E.g.

Please Login or Register  to view this content.

Then you could put "WBH" or "WBM" in Z1 and it should work.

WBD

That's done it. Thanks!

[random360]

Why do all the names have to be different? Why not use the same names on each tab, and set the scope to Worksheet instead of Workbook?

Because the input data is all on the same tab. There are 50+ sets of input data, all defined once there, and the output tabs reference portions of it.