Quick, clean, and to the point

Dynamic reference to table

Excel formula: Dynamic reference to table
Generic formula 

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:


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


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:


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:


which becomes:


and then:


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


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.

Dave Bruns
See also 

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.