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 equalCOUNT(Table1[names])
. - If
A1
=lastname
then the formula would equalCOUNT(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.