Log In
Register
Home
Active Member
April 23, 2020
Offline
Hi, I can use any array formula like unique, xlookup, etc. But however, i can't use # in my array references. The results shows an #REF! error. As I see in youtube, blog, etc, it should be work as well to put # as array references. Hope anybody can solve my problem 🙂
See Answer
Trusted Members
December 20, 2019
Offline
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
July 16, 2010
Offline
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.
Trusted Members
December 20, 2019
Offline
i cant believe that i type spilled instead of spilt and now i cant edit it. the shame of it!
July 16, 2010
Offline
😀 don't worry, American English use 'spilled'. It's only the Brits that use 'spilt' 🙂
Trusted Members
December 20, 2019
Offline
That's exactly what i was worried about
Active Member
April 23, 2020
Offline
Exactly, i want to use dynamic array, but show an error. Any idea how to use the # properly?
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Please have a look at this blog post, it also describes on how you use # character when referencing to spilled data.
https://www.myonlinetraininghu.....mic-arrays
Br,
Anders
July 16, 2010
Offline
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
Active Member
April 23, 2020
Offline
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. 🙂
1 Guest(s)
