Hi

I am fairly sure that you can only use the # on spilled ranges, so you use =UNIQUE(B2:B11), then if you want to sort the output from thge unique you can =SORT(D#)

Purfleet

Purfleet is correct. The # range operator only works on ranges that result from dynamic arrays that spill the data. They don't work on any old range.

i cant believe that i type spilled instead of spilt and now i cant edit it. the shame of it!

😀 don't worry, American English use 'spilled'. It's only the Brits that use 'spilt' 🙂

That's exactly what i was worried about

Exactly, i want to use dynamic array, but show an error. Any idea how to use the # properly?

Hello,

Please have a look at this blog post, it also describes on how you use # character when referencing to spilled data.

https://www.myonlinetraininghub.com/excel-dynamic-arrays

Br,
Anders

Hi Akh,

Like we already said, you can't force any old range to recognise the # spilled range operator. It will only work when the data in the range being referenced is the RESULT of another dynamic array formula that has spilled the results.

i.e. if the values in column b are generated by a dynamic array formula like FILTER etc. then the reference B3# will work. But if you just typed them in then you can't use B3#.

Hope that makes sense now.

Mynda

Hi Anders & Mynda,

Thanks for the feedback. But, i'm too newbie to understand how to use # properly. Could you give me a very simple data + formula that use # operator? 🙂

Thanks again. I just want to make sure that my Excel work properly. 🙂

I guarantee your Excel is working just fine. Please see screenshots attached for further examples.

Oh, now i see the different, hehe 🙂

Thank you so much.