# Why doesn't the spilled range operator work?

Occasional Contributor

# 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#) we get a #REF error. We're on the Monthly channel. Is it working for anyone else on the Monthly channel?

10 Replies

# Re: Why doesn't the spilled range operator work?

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

Typing such reference do you see the source spill selected?

# Re: Why doesn't the spilled range operator work?

No, I get a #REF error.

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

# Re: Why doesn't the spilled range operator work?

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

# Re: Why doesn't the spilled range operator work?

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.

# Re: Why doesn't the spilled range operator work?

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

# Re: Why doesn't the spilled range operator work?

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

# Re: Why doesn't the spilled range operator work?

@ACutler , you are welcome

# Re: Why doesn't the spilled range operator work?

ok I had the same problem but you can generate very easily your spilled data

# Re: Why doesn't the spilled range operator work?

just do an if(1;A:A;"") or similar...

# Re: Why doesn't the spilled range operator work?

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.