Dynamically reference a Named Table Column (via cell content) in Excel

Solution 1:

You nearly had it with INDIRECT(), but your logic was a little off. Try this instead:

=COUNT(INDIRECT("Table1["&A1&"]"))

The key to remember is that INDIRECT() only takes text (i.e., a string) for the first argument. Thus, you have to pass all parts of the table reference to the function as text.

Solution 2:

Using the INDIRECT method works, but is best avoided whenever possible as it is a volatile function and can have massive computational impact on your spreadsheet.

A non-volatile alternative that accomplishes what you want is to use INDEX/MATCH

COUNT(INDEX(Table1, 0, MATCH($A$1, Table1[#Headers], 0)))

-Tim

Solution 3:

first step setup a text cell which contains the name of the column you which to reference (say $A$1) $A$1 contains "Column2" for example.

then to find the count of the column which $A$1 refers to would be =Count(indirect("Table1[" & $a$1 & "]"))

this indirect method may be used to construct the all parts of the table reference and can for instance also be arranged to lookup from different tables

tip. if the $a$1 cell are validated as drop down list which points to the headers of the single table, then any changes to the number of columns in the table of any changes to the header titles will be picked up.