Dependent Array doesn't drop down after loading

Hi All

Having a bit of an issue where I have an Array formula (Unique(Filter)) and when I change a drop down it changes the array. works perfectly. however sometimes when I save and close it, then open it again and change the drop down it changes the figures and does the function fine, but the list of results is locked to the same length as the previous option.
So for EG I select "A". A has 4 results and it displays em fine.
I save and close. Then open and Select "B". B has 15 results. for some reason it only shows the top 4 results.

If I copy the formula, delete it, then past it in it works fine again.

This is a tool for people to use in work and so I'd really like to overcome it doing this if at all possible. It doesn't ALWAYS do it, but often does.

any idea whats causing it or how I overcome it??

Cheers all!
Qid

No worries mate, added now.

So we have three people, Bob, John and Frank.
when you open it and it was saved as Frank it shows all Franks bookings, as we expect.
However if you change this to Bob, who only has one booking, it shows a bunch of errors instead of only producing 1 line. If you select Frank who has 13 bookings, it only shows his top few. The array is locked in size.
If you go into the formula bar and copy the Array in B5. Delete that array. Then paste it back in, it resizes just fine.

Cheers guys
Qid

You need to clear column B from B5 down, then put the formula into B5 only & do not use Ctrl Shift Enter

Hi!

Ah yes this is what I mean about it working once you delete and re-enter it.

I actually never do the crtl shift enter on Unique or filter formulas cos I'm lazy haha.

If you do the above, it works perfectly, until you save it and open it and it does the same again

That's because you are saving it as an xls file. You need to use an xlsx or xlsm format.

You can also change the formulae in D onwards like

Formula:

Please Login or Register  to view this content.

and this will spill down as far as needed.

Ahh, yeah it's the file format that did it! interesting phrasing btw

Cheers
Qid

Glad to help & thanks for the feedback