Feb 06 2020 01:56 PM - edited Feb 06 2020 01:57 PM
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?
Feb 06 2020 02:08 PM
Yes, monthly channel shall be covered by dynamic array functionality.
Typing such reference do you see the source spill selected?
Feb 06 2020 02:15 PM
No, I get a #REF error.
But Dynamic Arrays are working because they spill if I do something like =$A2:$A11
Feb 06 2020 02:27 PM
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#.
Feb 06 2020 02:42 PM
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.
Feb 06 2020 02:43 PM
Thank you, I understand now. I was hoping the function could be used to reference static lists but it only works on spilled data.
Feb 06 2020 02:44 PM
Thanks, I see now how I had misunderstood the documentation.
Mar 23 2021 11:35 AM
Mar 23 2021 11:37 AM
Mar 23 2021 01:47 PM
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.
Jun 23 2022 05:16 PM