Why doesn't the spilled range operator work?

%3CLINGO-SUB%20id%3D%22lingo-sub-1157102%22%20slang%3D%22en-US%22%3EWhy%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157102%22%20slang%3D%22en-US%22%3E%3CP%3EDynamic%20arrays%20seem%20to%20have%20been%20working%20on%20all%20our%20office%20PCs%20for%20a%20couple%20of%20weeks%20and%20formulas%20are%20spilling%20as%20expected%20but%20if%20we%20try%20to%20use%20the%20spilled%20range%20operator%20in%20a%20reference%20(like%20%24A2%23)%20we%20get%20a%20%23REF%20error.%20We're%20on%20the%20Monthly%20channel.%20Is%20it%20working%20for%20anyone%20else%20on%20the%20Monthly%20channel%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1157102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157133%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F548003%22%20target%3D%22_blank%22%3E%40ACutler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20monthly%20channel%20shall%20be%20covered%20by%20dynamic%20array%20functionality.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETyping%20such%20reference%20do%20you%20see%20the%20source%20spill%20selected%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20421px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169522iE2B17E564CAD93BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157153%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20I%20get%20a%20%23REF%20error.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ACutler_0-1581027154462.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169523i26C63E308D246B0D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22ACutler_0-1581027154462.png%22%20alt%3D%22ACutler_0-1581027154462.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBut%20Dynamic%20Arrays%20are%20working%20because%20they%20spill%20if%20I%20do%20something%20like%20%3D%24A2%3A%24A11%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ACutler_1-1581027283481.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169524i7E3672C80F011BE0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22ACutler_1-1581027283481.png%22%20alt%3D%22ACutler_1-1581027283481.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157173%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F548003%22%20target%3D%22_blank%22%3E%40ACutler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20you%20have%20no%20spill%20in%20A2%3AA11%2C%20that's%20just%20a%20range.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20instead%20of%20this%20range%20you%20enter%20in%20A2%20formula%20like%20%3DSEQUENCE(10)%20it%20will%20return%20the%20spill%2C%20and%20after%20that%20you%20may%20use%20the%20reference%20on%20this%20spill%20like%20%3DA2%23.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157208%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F548003%22%20target%3D%22_blank%22%3E%40ACutler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20far%20as%20I%20know%2C%20%23%20notation%20is%20used%20to%20refer%20the%20spilled%20range.%3C%2FP%3E%3CP%3EAs%20per%20the%20screenshot%20in%20your%20last%20post%2C%20since%20C2%3AC11%20is%20a%20spilled%20range%20after%20you%20placed%20the%20formula%20%3DA2%3AA11%20in%20cell%20C2%20so%20if%20you%20type%20%3DC2%23%20in%20any%20cell%20say%20in%20cell%20D2%20(remember%20not%20to%20press%20Enter%2C%20just%20type%20it)%2C%20you%20will%20find%20that%20the%20range%20C2%3AC11%20would%20be%20selected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157210%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20I%20understand%20now.%20I%20was%20hoping%20the%20function%20could%20be%20used%20to%20reference%20static%20lists%20but%20it%20only%20works%20on%20spilled%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157215%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20I%20see%20now%20how%20I%20had%20misunderstood%20the%20documentation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157224%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F548003%22%20target%3D%22_blank%22%3E%40ACutler%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2230514%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20the%20spilled%20range%20operator%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2230514%22%20slang%3D%22en-US%22%3Eok%20I%20had%20the%20same%20problem%20but%20you%20can%20generate%20very%20easily%20your%20spilled%20data%3C%2FLINGO-BODY%3E
Occasional Contributor

Dynamic arrays seem to have been working on all our office PCs for a couple of weeks and formulas are spilling as expected, but if we try to use the spilled range operator in a reference (like $A2#) we get a #REF error. We're on the Monthly channel. Is it working for anyone else on the Monthly channel?

10 Replies

@ACutler 

Yes, monthly channel shall be covered by dynamic array functionality.

 

Typing such reference do you see the source spill selected?

image.png

 

@Sergei Baklan 

No, I get a #REF error.

ACutler_0-1581027154462.png

But Dynamic Arrays are working because they spill if I do something like =$A2:$A11

ACutler_1-1581027283481.png

@ACutler 

I guess you have no spill in A2:A11, that's just a range. 

If instead of this range you enter in A2 formula like =SEQUENCE(10) it will return the spill, and after that you may use the reference on this spill like =A2#.

@ACutler 

As far as I know, # notation is used to refer the spilled range.

As per the screenshot in your last post, since C2:C11 is a spilled range after you placed the formula =A2:A11 in cell C2 so if you type =C2# in any cell say in cell D2 (remember not to press Enter, just type it), you will find that the range C2:C11 would be selected.

@Sergei Baklan 

Thank you, I understand now. I was hoping the function could be used to reference static lists but it only works on spilled data.

@Subodh_Tiwari_sktneer 

Thanks, I see now how I had misunderstood the documentation.

@ACutler , you are welcome

ok I had the same problem but you can generate very easily your spilled data
just do an if(1;A:A;"") or similar...

@Oswe_ 

An issue was not in creating the spill, but in applying to the range reference notation used for spills. It doesn't work such way.