Forum Discussion
Dynamic Referencing Tables in Excell
Got an odd question that I'm not sure is even possible. I want to dynamically reference a table in order to look up values. I have a number of project trackers, each on their own sheet in a workboo...
I just learnt from Sergei's post, you may try it, e.g. create a name ProjectTable, and point to a cell in the worksheet that contain the project table name.
Dynamic reference to the project name cell, you may create the name like
=EVALUATE(INDIRECT("B3") & "[#ALL]")
where B3 is the example of yours. As you may have multiple worksheets, you may not want to create multiple names. Since name manager will auto add worksheet name to the cell reference if you do not provide, using INDIRECT function can let the name be used in different worksheets.
As this approach is using name to pretent a table, it can't be used as a table, e.g. you cannot do it like this:
=ProjectTable[Column1]
That is why I make the ProjectTable name to include "[#ALL]", and use INDEX function when needed