Aug 14 2021 09:41 AM
Aug 14 2021 09:41 AM
i need a bit of help to understand something with structured reference in excels Table
=DeptSales[Sales Amount],DeptSales[Commission Amount]
A combination of two or more columns
, (comma) union operator
I can't use this thing, the , comma union operator doesn t work, but i can use the : (colon) as below and i really don't understand why.
All of the cells in two or more adjacent columns
: (colon) range operator
Thanks for your help and sorry for mybad english
Aug 14 2021 10:57 AM
Aug 14 2021 11:23 AM
It depends on what you are trying to do, i.e.
If you're trying to get a range:
If you're trying to do an equation:
so what are you trying to do? show the desired result
Aug 14 2021 01:19 PM
Structured Reference Union does not work as described in
I have posted a question in:
Aug 14 2021 01:55 PM
That is not a problem with structured references. If you try it with regular cell references you get a #VALUE! error. The union operator only works in certain functions.
And you are giving conflicting information: In G2 you want Sales and Goal, in N2:O2 you show Sales and % of Goal.
Aug 14 2021 01:58 PM
yes my bad, i just made a quick exemple
but it still prove the help page doesn't work
Aug 14 2021 02:06 PM
Support page describes union with this example:
and it does not work, I did a search for the word union:
So as it is currently described in the support page:
it does not seem to work as "described" in the support page.
Aug 14 2021 02:15 PM
Aug 15 2021 01:21 PM
Union works with structured reference exactly the same way as with non-structured references.
Simplest case of using union
=SUM(C2:C7,E2:E7) or =SUM(DeptSales[Sales Amount],DeptSales[Commission Amount])
Not sure where union is explained in documentation, but you may find in blogs. Perhaps oldest one is
Another story is that only few functions support using of union - SUM(), COUNTA(), SMALL(), MAX() and like.
Aug 15 2021 02:21 PM - edited Aug 15 2021 02:29 PM
It's a different story when Union reference is wrapped within another excel function, however I am talking about how it is described in the support page. Which is by itself as if referencing a couple of range reference to induce a spilled range consisting of the 2 different ranges that comprises of the Union Structured reference. The user inquiry is very specific about
"understanding how it is described in the support page"