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

microsoft excelmicrosoft-excel-2007worksheet-function

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.

Best Answer

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.

Related Question