[Solved] I cant use Item specifier i.e #All and #Headers

[Mike_Vu_Ngo]

Hello. I'm learning the basics of Excel and was reading this Excel structured reference
The problem is I cant replicate this in my own worksheet. I have named the table and typed the formula correctly. But both Tablename[#All] and Tablename[#Headers] returned as #VALUE! . However, the formula worked on my wife's laptop. My version is Excel 2019 Professional.
Any ideas to fix this? Thank you!

[#2]

@Mike_Vu_Ngo 

Those references return multiple cells. Excel in Microsoft 365 and Office 2021 handle this automatically, but older versions don't.

You can use =Data[[#Headers],[Sales Person]] and drag it to the right.

[#3]

What you show appears to be correct. Perhaps you could try re-entering the formulas by typing the "=" followed by selecting the desired region with the mouse.

[#4]

@Hans VogelaarHello. I tried what you said and the result is... interesting.
- Data[#Headers] only works if the cell I entered the formula is directly on the same column of the header i.e Sales Person, Sales Amount. etc...
- Data[[#Headers],[Sales Person]] works anywhere (only return the headers), as I drag right it shows other headers and works fine.

- Data[[#data],[Sales Person]] don't work. I tried changing the name of table, can't make it work.
- Data[Sales Person] show the value of cell of the same row and under the Sales Person header. So it must be placed on the same row of the table for it to work.

In conclusion I can get around the problem. However, still can't make [#all],[#data],[#totals] to work.

[#5]

hello Peter. I tried what you said and it doesn't work. You can read my follow up at my reply to Hans.

[#6]

@Mike_Vu_Ngo 

@Hans Vogelaar has correctly identified your problem; I was simply suggesting a check for possible typing errors.

From what you describe, your wife's laptop has Excel 365, which treats arrays correctly.  Traditional spreadsheets (up to and including 2019) intentionally reduce an array reference to a single cell that happens to be in the same row or column as the formula cell; hence the need for 'and fill down' with each copy of the formula returning a single value.

I used to use CSE arrays or write the formulas as defined Names to avoid traditional spreadsheet behaviour but practically all other users see the cell-by-cell processing as wonderful in its simplicity.  Oddly, they do not appreciate my describing the result as a 'steaming pile of junk'.

[#7]

yeah it seems the case. I will mark this as solved. Thank you very much!