Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
What version of Excel are you using? – CharlieRB Aug 13 '12 at 16:07
    
Currently using Excel 2007 – rcphq Aug 13 '12 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 at 6:21
up vote 7 down vote accepted

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.

share|improve this answer
    
indeed that worked, i needed to reference the entire table. this worked! thanks! – rcphq Aug 13 '12 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)))

-Tim

share|improve this answer
    
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 '14 at 19:00

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.

share|improve this answer
    
(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. – Scott May 26 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:

=COUNT(Table1[Column1])

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

=COUNT(Table1[Sales])
share|improve this answer
    
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 '12 at 19:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .