Help needed for understanding with structured reference

%3CLINGO-SUB%20id%3D%22lingo-sub-2648446%22%20slang%3D%22fr-FR%22%3EHelp%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648446%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3Ei%20need%20a%20bit%20of%20help%20to%20understand%20something%20with%20structured%20reference%20in%20excels%20Table%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%3DDeptSales%5BSales%20Amount%5D%2CDeptSales%5BCommission%20Amount%5D%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EA%20combination%20of%20two%20or%20more%20columns%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%2C%20(comma)%20union%20operator%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3EC2%3AC7%2C%20E2%3AE7%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20use%20this%20thing%2C%20the%20%2C%20comma%20union%20operator%20doesn%20t%20work%2C%20but%20i%20can%20use%20the%20%3A%20(colon)%20as%20below%20and%20i%20really%20don't%20understand%20why.%3C%2FP%3E%3CTABLE%20width%3D%22723px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22232.75px%22%3E%3CP%20class%3D%22%22%3E%3DDeptSales%5B%5BSales%20Person%5D%3A%5BRegion%5D%5D%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22283.453px%22%3E%3CP%3EAll%20of%20the%20cells%20in%20two%20or%20more%20adjacent%20columns%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22153.781px%22%3E%3CP%3E%3A%20(colon)%20range%20operator%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2252.0156px%22%3E%3CP%3EA2%3AB7%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%20and%20sorry%20for%20mybad%20english%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2648446%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648546%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648546%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fusing-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fusing-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648566%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648566%22%20slang%3D%22en-US%22%3Ethanks%20for%20your%20answer%20but%20i%20already%20tried%20what's%20in%20this%20link%2C%20nothing%20work%20for%20the%20combinaison%20of%20two%20or%20more%20colomn%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648601%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128508%22%20target%3D%22_blank%22%3E%40Adrira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edo%20you%20have%20a%20sample%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648619%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648619%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128508%22%20target%3D%22_blank%22%3E%40Adrira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20depends%20on%20what%20you%20are%20trying%20to%20do%2C%20i.e.%3C%2FP%3E%3CP%3EIf%20you're%20trying%20to%20get%20a%20range%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1628965129021.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303276i6A469CE78878823D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1628965129021.png%22%20alt%3D%22Yea_So_0-1628965129021.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20trying%20to%20do%20an%20equation%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_1-1628965363493.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303277i4A1334247F74F29C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_1-1628965363493.png%22%20alt%3D%22Yea_So_1-1628965363493.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20what%20are%20you%20trying%20to%20do%3F%26nbsp%3B%20show%20the%20desired%20result%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648621%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648621%22%20slang%3D%22en-US%22%3E%3CP%3Ehere%20a%20sample%20file%20and%20what%20i%20wanna%20do%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648659%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648659%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128508%22%20target%3D%22_blank%22%3E%40Adrira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20moved%20%5BGoal%5D%20before%20%5BSales%5D%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_1-1628969376697.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303280i55075157FC727622%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_1-1628969376697.png%22%20alt%3D%22Yea_So_1-1628969376697.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20attached%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648688%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128508%22%20target%3D%22_blank%22%3E%40Adrira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStructured%20Reference%20Union%20does%20not%20work%20as%20described%20in%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fusing-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e%23%3A~%3Atext%3DStructured%2520references%2520also%2520appear%2520when%2520you%2520create%2520a%2Cof%2520typing%2520their%2520cell%2520reference%2520in%2520the%2520formula.%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUsing%20structured%20references%20with%20Excel%20tables%20-%20Office%20Support%20(microsoft.com)%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20posted%20a%20question%20in%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_excel-mso_win10-mso_365hp%2Fstructured-reference-union-does-not-work%2F0f78fd96-1323-4a2a-aaaa-344586fa1d2a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EStructured%20Reference%20Union%20does%20not%20work%20-%20Microsoft%20Community%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648712%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128508%22%20target%3D%22_blank%22%3E%40Adrira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20not%20a%20problem%20with%20structured%20references.%20If%20you%20try%20it%20with%20regular%20cell%20references%20you%20get%20a%20%23VALUE!%20error.%20The%20union%20operator%20only%20works%20in%20certain%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20you%20are%20giving%20conflicting%20information%3A%20In%20G2%20you%20want%20Sales%20and%20Goal%2C%20in%20N2%3AO2%20you%20show%20Sales%20and%26nbsp%3B%25%20of%20Goal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648713%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20needed%20for%20understanding%20with%20structured%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyes%20my%20bad%2C%20i%20just%20made%20a%20quick%20exemple%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20it%20still%20prove%20the%20help%20page%20doesn't%20work%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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"