Forum Discussion

Nicholas Schretter's avatar
Nicholas Schretter
Copper Contributor
Jan 05, 2018

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 workbook.  I have a blank tacker template and a macro that copies, renames it as a the new project, adds the project name to a list, and some other things.  On the template there is a table that will house the tasks and be renamed when a new tracker is made.  A simple thing I want to do is count all the tasks that are not started (0% in the Progress Column).  Normally I'd just use =COUNTIF(Table1[Progress],"=0") but the table name is going to change based on the name of project.  How do I write any formula so that when I would be putting the value 'Table1[XXX]' I can have Table1 be a value that is in the project name cell elsewhere on the sheet (lets say B3)?  For example if the project name is ToolBoxes, the table would be called ToolBoxes and the formula (for this scenario) would evaluate out in the end to =COUNTIF(ToolBoxes[Progress],"=0"). All the column headers for the table would be consistent and the project name will always be in B3.

Formulas & Functions
tips and tricks

4 Replies

Sort By
  • Willy Lau's avatar
    Willy Lau
    Iron Contributor
    Jan 06, 2018

    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

Resources