Forum Discussion
Why doesn't the spilled range operator work?
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#) ...
Yes, monthly channel shall be covered by dynamic array functionality.
Typing such reference do you see the source spill selected?
No, I get a #REF error.
But Dynamic Arrays are working because they spill if I do something like =$A2:$A11
- This is what I am trying to achieve, and how I interpreted the doco. What did you end up doing?
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.
Thanks, I see now how I had misunderstood the documentation.
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#.
Thank you, I understand now. I was hoping the function could be used to reference static lists but it only works on spilled data.
- just do an if(1;A:A;"") or similar...
