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

Question

How do I reference an Excel table column dynamically in Excel 2007? I want to reference a named column of a named table and the referenced column will vary with the value of a cell.

I have a table in Excel (let's call it Table1). I want to reference one of its columns and COUNT the numbers in that column. I want to identify the referenced column dynamically from a value in another cell (A1) so that I can achieve the following result: When I change A1, the formula that counts Table1[DynamicallyReferencedColumnName] gets updated to the new reference.

Example:

• If A1 = names    then the formula would equal COUNT(Table1[names]).
• If A1 = lastname then the formula would equal COUNT(Table1[lastname]).

I tried using =COUNT(Table1[INDIRECT("$A$1")]), but Excel says the formula contains an error.

How can I do this?

---

P.S. I found this MSDN document that may be relevant: Excel recalculation.

Answer 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.

Answer 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

Answer 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.

Answer 4

Here is a very good article regarding structured references in Excel. It would appear this works a little differently in Excel 2010 than in Excel 2007.

Using structured references with Excel tables

Basically you will reference the table and then the column within the table.

In this example you can reference Table 1 Column 1 like this:

=COUNT(Table1[Column1])

You can name the header columns in your table. If I renamed Column1 to Sales the formula would become:

=COUNT(Table1[Sales])