How do I reference an Excel Table column dynamically in Excel 2007? ie: i wanna reference a named column of a named table and what table it is 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 (Let's call it **column1**) 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.

I tried using `=Count(Table1[INDIRECT("$A$1")])`

but Excel says the formula contains an error.

Example:

A1 = namesthen the formula would equalCount(Table1[names]).

A1 = lastnamethen the formula would equalCount(Table1[lastname]).

Edit for answers:

**Volatile solution**

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

**Non-volatile solution** (Generally better)

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

Extra reading on volatile functions: Excel recalculation (MSDN)