Indirectly Refer to Table Columns


Previously, we explored using the INDIRECT function to refer to various tables in a workbook. In this follow-up post, we’ll expand the discussion and refer to individual table columns.


Let’s start with our objective. We have several tables in a workbook. They have the same structure and store department data. For example, here is the Department A table.

Excel Table by Jeff Lenning

We would like to be able to select the table AND the column from drop-down controls, and have Excel use the selected table and column in a simple SUM function as illustrated below.

INDIRECT by Jeff Lenning

Alright, let’s get to it.

Structured Table References

First up, let’s figure out how Excel’s structured table references work. A Structured Table Reference (STR) allows us to refer to a specific area within a table, such as a specific column. STRs begin with the table’s name followed by the specific area enclosed in square brackets [].

Referencing a specific column is accomplished like this: TableName[ColumnName]. For example, to refer to the Q1 column in the DeptA table, we would use: DeptA[Q1].

In addition to referring to specific columns, we can refer to specific rows. For example, to refer to just the header row of the DeptA table, we could use DeptA[#Headers]. To get a list of the available STRs, type the table’s name in a formula and then type the open square bracket. Excel will display a list of valid STRs in the auto-complete list, as shown below.

Auto-complete List by Jeff Lenning

Now that we know the anatomy of STRs, let’s move on to the drop-down cells.

Data Validation

To create the drop-down controls, we’ll use the data validation feature.

The drop-down control in C6 is generated with the Data Validation feature by allowing a list equal to the table names. Setting this up was discussed in the previous post.

The drop-down control in C7 is generated with the Data Validation feature by allowing a list equal to the column names. To do so, select the input cell C7 and then the Data Validation ribbon command. Set it to allow a list equal to the column labels you’d like to be displayed, as shown below.

Data Validation by Jeff Lenning


At this point, all that remains is to write the formula. We need to create the desired reference with a text string and then pass it to the INDIRECT function. The INDIRECT function will convert the text string into a valid Excel reference.

For example, let’s say that we ultimately wanted to sum up the Q1 column of the DeptA table. We could use the following formula.


But, rather than entering the STR directly into the formula, we want to store the table name in cell C6 and the column name in C7. So, we would build the STR using the concatenation operator (&), as follows:

=C6 & "[" & C7 & "]"

Note: spaces are shown in the formula above to make it easier to read.

Assuming DeptA is stored in C6 and Q1 is stored in C7, the formula above returns the desired STR, DeptA[Q1].

To convert the STR into a valid Excel reference, we’ll use the INDIRECT function, as follows.


Finally, we’ll sum the cells in the reference with the SUM function, as follows.


This will compute the sum for the selected table and column, as illustrated below.

INDIRECT by Jeff Lenning

If you have any additional ideas or methods, please share by posting a comment below!

Additional Resources

  • Sample Excel File: IndirectTableColRef
  • Dynamically reference table columns with INDEX/MATCH: SUMIFS with Dynamic Table data
  • Related post: Referring to Tables Indirectly
  • Note: our tables had the same column headers; if they were different, you could use Data Validation and allow a list equal to =INDIRECT(C6&”[#Headers]”) so that the column drop-down would provide the column headers found in the table identified in C6

This article was written by jefflenning


  1. Barry

    I’m using your approach to create dynamic features built into a dashboard. I’m attempting to make the STR [column specifier] be determined based on the output of a form control….. without success. Will this approach work with other formulas such as SUMPRODUCT.

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.