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.