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.


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

  • What version of Excel are you using?
    – CharlieRB
    Aug 13, 2012 at 16:07
  • Currently using Excel 2007
    – rcphq
    Aug 13, 2012 at 16:15
  • I think this question can also be rephrased as: "reference a dynamic component of a structured reference in excel".
    – jiggunjer
    Jun 30, 2016 at 6:21

4 Answers 4


You nearly had it with INDIRECT(), but your logic was a little off. Try this instead:


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.

  • indeed that worked, i needed to reference the entire table. this worked! thanks!
    – rcphq
    Aug 13, 2012 at 19:38

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)))


  • Excelent, i actually came across this method later on and have been using it since. Coincidentially i started noticing a huge slowdown on the older spreadsheets where i was using indirect on over a dozen sheets. Thanks!
    – rcphq
    Feb 7, 2014 at 19:00
  • I've been trying to figure this out for months; I had a hunch index/match could do it but never got the formula right. Thank you!!
    – Travis P
    Dec 26, 2020 at 18:02

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.

  • 1
    (1) First step: put the name of the desired column into a cell such as A1.  The OP has already done this, and says so in the question.  (2) The second step is an exact copy of Excellll's answer.  (4) Configuring A1 as a dropdown of the valid column names looks like a good idea, but I don't understand the rest of the last paragraph of the answer. May 26, 2016 at 11:50

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.

Generic Excel Table

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


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

  • Yeah my table is properly named with the different columns, what i want is a method to TELL excel what column i want depending on a cell value, something like this =COUNT(Table1[A1]) Where A1 is the cell i want to contain the columnname
    – rcphq
    Aug 13, 2012 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.