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.
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.
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.
Now that we know the anatomy of STRs, let’s move on to the drop-down cells.
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.
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.
If you have any additional ideas or methods, please share by posting a comment below!
- 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