Aug 14 2021 09:41 AM
Hi,
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 | C2:C7, E2:E7 |
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.
=DeptSales[[Sales Person]:[Region]] | All of the cells in two or more adjacent columns | : (colon) range operator | A2:B7 |
Thanks for your help and sorry for mybad english :)
Aug 14 2021 10:57 AM
Aug 14 2021 11:06 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 11:31 AM
here a sample file and what i wanna do
Aug 14 2021 12:30 PM
Aug 14 2021 01:19 PM
Structured Reference Union does not work as described in
Using structured references with Excel tables - Office Support (microsoft.com)
I have posted a question in:
Structured Reference Union does not work - Microsoft Community
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
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:
Using structured references with Excel tables - Office Support (microsoft.com)
it does not seem to work as "described" in the support page.
Aug 14 2021 02:15 PM
Adrira's idea was to have a desired result as shown in a pivot table:
only using structured table reference
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
Union and Intersect – Daily Dose of Excel
Another story is that only few functions support using of union - SUM(), COUNTA(), SMALL(), MAX() and like.
Aug 15 2021 01:23 PM
Aug 15 2021 02:21 PM - edited Aug 15 2021 02:29 PM
Structured Reference Union does not work as described in Office - Microsoft Community
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"