0

In an Excel table, I can refer to a "first_column" through the structured reference:

=Table1[[#This row];[first_column]]

In a complex formula it may be hard to read.

I noticed that:

=[first_column]

also works!

Before spreading the word, I would like to check it out on the docs, and... I didn't find anything.

Before starting using this feature massively, I need to be sure that's it's a reliable one. Can you provide links to documentation or other authoritative information?

enter image description here

enter image description here

Improve this question

1 Answer 1

Reset to default
1

=Table1[[#This row];[first_column]] and =[first_column] are not the same. The first one refers to one cell. In your example the cell in [first_column] of the same row. The second one refers to the whole column data of the column [first_column]. In your example 3 cells {11;22;33}.

You will see this if you are using =SUM(Table1[[#This row];[first_column]]) versus =SUM([first_column]).

There =SUM([first_column]) is the same as =SUM({11;22;33}). But =[first_column] in one cell would be the same as ={11;22;33} in that one cell. And this only shows the first of the three values if applied in one cell only or the respective value of the row if applied to a range of three cells one below the other.

See: Using structured references with Excel tables.

The really shortcut for =Table1[[#This row];[first_column]] would be =Table1[@[first_column]] or [@[first_column]] if used in the same table. But this is not possible in Excel 2007.

All formulas are using semicolon as the list delimiter. Default would be comma for en_US Excel. But since it had be used in the question also, I have take it.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Ok, i tried SUM([first_column]) and it gives the same result for all rows. But how is it possible that it works then? I've tried defining a regular interval, say from A1:A5, i've filled with numbers from 1 to 5 and i gave it a name: "wow". =sum(wow) gives 15 but =wow gives always 1. There is something going on here, something table-specific.
Nothing table-specific but something array-specific. See my supplement.
sorry, what i don't understand is how is it possible that ={11;22;33} shows the second value in the second row. It should show always the first one.
No, if an array ={11;22;33} is applied to three cells one below the other, then each of the three cells shows the respective value of the row.
In A1 put 11, in A2 put 22, in A3 put 33. Now in B1 put the formula =$A$1:$A$3 and fill down that formula. In B1 it shows 11, in B2 it shows 22, in B3 it shows 33, nevertheless it is always the same =$A$1:$A$3. In B4 it shows #VALUE! because there is not more a respective value for the 4th row.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.