Why doesn't the spilled range operator work?

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?

11 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.

This is what I am trying to achieve, and how I interpreted the doco. What did you end up doing?