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.

Objective

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

INDIRECT

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.

=SUM(DeptA[Q1])

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.

=INDIRECT(C6&"["&C7&"]")

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

=SUM(INDIRECT(C6&"["&C7&"]"))

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 Jeff Lenning

8 comments:

  1. Barry
    Reply

    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.

  2. Ian
    Reply

    I am trying to do something slightly different but similar to the above. It seems like I should be able to use index/match but I can’t get it to work. Do you have any suggestions? I want to create a conditional lookup based on something like the following table:
    Product Name
    Fruit Apple
    Fruit Pear
    Veg Carrot
    Veg Leek

    What I would like to do is create a conditional lookup so that if someone types Fruit into cell A1, then the dropdown on cell B1 only shows the options Apple and Pear. I know I can do this by creating a table for Fruit and another table for Veg but I would like to keep all the data in a single table. The reason for this is the actual problem I am solving is more complex than my example with 4 columns ie 4 levels of data. Any suggestions would be gratefully received.

    Ian

  3. karen
    Reply

    I’ve spent several evenings trying to figure out how to convince Excel that my chosen dropdown value needed to be read as a columnheader, so I could create a second dropdown based on that column. Without any previous knowledge of Excel and the proper terminology it has been extremely difficult to figure out.
    This bit “So, we would build the STR using the concatenation operator (&), as follows:” finally got me my solution. THANK YOU SO MUCH!

    1. Jeff Lenning Post author
      Reply

      🙂

  4. Bruno Hecquard
    Reply

    Hello,
    I’ve build a spreadsheet containing one master data table with one column per month and one student name per individual row in the first column. The month‘s headers are named using thé twelve months names. There are twelve additionnal tabsheets, named as well using the same months names.
    My goal is to retrieve the student‘s hours per month stored in each monthly tabsheet, and to report them into the master data table.
    I succeeded doing the job using a vlookup and an indirect function which takes the header string into account in order to search into the relevant tabsheet., i.e., INDIRECT(J$1), where J1 contrains „April“ for example.
    To make it more robust, I would like to use the [#Headers] and [#This Column] instead of the hard coded cell reference.

    Would you know which syntax to use for such a purpose?

    1. Jeff Lenning Post author
      Reply

      Within the table, to refer to the header cell within a column, you should be able to use the following:
      =Table1[#Headers]
      When I test it here, it returns the corresponding column label correctly for each table column.
      Hope this helps,
      Jeff

Leave a Reply

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