Help needed for understanding with structured reference

Copper Contributor

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 :)

14 Replies
thanks for your answer but i already tried what's in this link, nothing work for the combinaison of two or more colomn

@Adrira 

 

do you have a sample file

@Adrira 

 

It depends on what you are trying to do, i.e.

If you're trying to get a range:

Yea_So_0-1628965129021.png

 

If you're trying to do an equation:

Yea_So_1-1628965363493.png

 

so what are you trying to do?  show the desired result

here a sample file and what i wanna do 

@Adrira 

 

I moved [Goal] before [Sales]

Yea_So_1-1628969376697.png

 

File attached below

 

 

 

@Adrira 

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.

 

@Detlef Lewin 

 

yes my bad, i just made a quick exemple

 

but it still prove the help page doesn't work 

@Detlef Lewin 

 

Support page describes union with this example:

 

Yea_So_0-1628975058401.png

and it does not work, I did a search for the word union:

Yea_So_1-1628975112535.png

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.

@Adrira  @Detlef Lewin 

 

Adrira's idea was to have a desired result as shown in a pivot table:

 

Yea_So_0-1628975696340.png

only using structured table reference

@Yea_So 

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.

@Adrira 

As variant

=FILTER(SalesData[#All],{0,1,0,1})

@Sergei Baklan 

 

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"