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


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


Typing such reference do you see the source spill selected?



@Sergei Baklan 

No, I get a #REF error.


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



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


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.


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


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?