Summary

To build a formula with a dynamic reference to an Excel Table name, you can use the INDIRECT function with concatenation as needed. In the example shown, the formula in L5 is:

``````=SUM(INDIRECT(K5&"[Amount]"))
``````

Which returns the sum of Amounts for three tables named "West", "Central", and "East".

Generic formula

``=SUM(INDIRECT(table&"[column]"))``

Explanation

In this example, the goal is to create a dynamic reference to an Excel Table in a formula. In other words, create a formula that can refer to an Excel table by name as a variable. The easiest way to do this in Excel is to assemble the reference as a text value using concatenation, then use the INDIRECT function to convert the text reference into a proper Excel reference.

In the example shown, the formulas in L5:L7 behave like these simpler formulas:

``````=SUM(West[Amount])
=SUM(Central[Amount])
=SUM(East[Amount])
``````

However, instead of hardcoding the table into each SUM formula, the table names are listed in column K, and the formulas in column L use concatenation to assemble a reference to each table. This allows the same formula to be used in L5:L7.

The trick is the INDIRECT function to evaluate the reference. We start with:

``````=SUM(INDIRECT(K5&"[Amount]"))
``````

which becomes:

``````=SUM(INDIRECT("West"&"[Amount]"))
``````

and then:

``````=SUM(INDIRECT("West[Amount]"))
``````

The INDIRECT function then resolves the text string into a proper structured reference:

``````=SUM(West[Amount])
``````

And the SUM function returns the final result, 27,500 for the West region.

Note: INDIRECT is a volatile function and can cause performance issues in larger, more complex workbooks.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.