Preview of Dynamic Arrays in Excel

Brass Contributor

Nov 17, 2018

Hi Joe,

I've just got the Dynamic Arrays last week. And it is simply awesome! Super COOL! I do hope the corporate world will move to Office 365 quicker so that we can use the full potential of Excel anywhere, not just at home. :P

I've encountered a strange behaviour in Data Validation using Dynamic Array.

What I did was to create a "Shrinking Dynamic Dropdown" - Once an item is selected, it will be gone for the next input. With Dynamic Arrays, it can be achieved easily in just a couple of minutes. However I encountered an unexpected behaviour - if I input a value through manual typing, it would trigger a recalculation before validation... which makes my trick fail... The trick works well if I select an value from the dropdown...

In short, my observation is:

Using dropdown menu: Validation first, recalculation afterward
Using manual input: Recalculation first, validation afterward (and hence the validation failed the input)

I've described the situation in more details in my post:

https://wmfexcel.com/2018/11/14/dynamic-shrinking-dropdown-with-dynamic-arrays-in-excel-365/

FYI, I didn't encounter this when using old tricks of array formulas.

I am not sure why it is... and I do not know where and who I should report this to... So I leave you a comment here. :)

Cheers,

Blog Post