Nov 23 2021 07:55 AM - edited Nov 24 2021 04:34 AM
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!
Nov 23 2021 08:05 AM
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.
Nov 23 2021 08:15 AM
Nov 23 2021 07:04 PM - edited Nov 23 2021 07:21 PM
@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.
Nov 23 2021 07:06 PM
Nov 24 2021 01:33 AM
@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'.
Nov 24 2021 04:33 AM