How do I refer to the header of this Table Column?

[UniMord]

I'd like to be able to refer to the name of the current Table column in a formula - is there a structured way of doing this, or am I stuck with using a regular reference?

 

[Norie]

If you start typing a formula in a cell and select a column in the table for one of it's references Excel should replace it with something from the sheet.

For example I got this formula when summing the values of the column with header Close in Table1.

=SUM(Table1[[#All],[Close]])

I don't quite know if that can be called a (dynamic) named range, but it will work.

There's probably something somewhere that explains the syntax a bit more.

 

[UniMord]

Hi Norie! I apologize for not replying promptly - I was away.
I see from your answer, that my question needs to be explained better.

What I'd like to do, and it might not be possible, is to refer to the NAME of the current column, similar to the way the current row is referred to with "@" (XL2010), or [#This Row] (XL2007). Suppose I have several columns in ThisTable, and I use those same column names in OtherTable. It would be convenient for me if I were able to say:

=INDEX(OtherTable, [Customer], MATCH(NAMEOFCURRENTCOLUMN, OtherTable[#Headers], 0))

This would generically find the correct column in the other table based on the name of the current column.

If I wanted to use brute force (and this can be expensive in a large table), I could express it like this:

=INDEX(OtherTable, [Customer], MATCH(INDEX(ThisTable[#Headers], COLUMN()), OtherTable[#Headers], 0))

If I'd just stop insisting on using purely structured references, The job can also be done by mixing in an unstructured reference like this:

=INDEX(OtherTable, [Customer], D$1, OtherTable[#Headers], 0)

But, I was hoping there was a direct, structured way of accomplishing this.

Thanks for your help!

 

[jkpieterse]

The only way I could use structured refs was by removing the column identifier:

=Table1[#Headers]

If you use this syntax below the table, Excel will know to get the column name from the same column.

 

[UniMord]

Thanks! That's exactly what I wanted, and exactly what Help didn't bother explaining.

 

[Norie]

UniMord

Glad you found an answer.

I totally agree with you about the help files regarding tables, they are practically useless.

I've learned more with trial and error with formulas and code than I've learned from them - and that's both Online and Offline versions.

It's actually a bit of a shame because they seem to have improved tables somewhat.

Mind you there's plenty of things they could add.

 

[Fazza]

Though not what you want, UniMord, it might interest you still: if using SQL the header is usable as the field name. Such as for external data queries (query tables) or in pivot tables (using external data approach) or recordsets via ADO or DAO. regards

 

[jkpieterse]

Maybe you'll find this article instructive:

www.jkp-ads.com/articles/excel2007tables.asp