Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 = names then the formula would equal Count(Table1[names]).

A1 = lastname then the formula would equal Count(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)

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

4 Answers 4

up vote 5 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

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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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