I have a dynamic list of category options I want to present in some cells using a data validation drop down. This list is ('admin') user-editable, and is located on a sheet named Config in column D. The list has a header cell in D2 and starts at cell D3, and can be as long as needed.
I wrapped the list in a named range called 'Categories', which has the formula:
=Config!$D$3:OFFSET(Config!$D$3,COUNTA(Config!$D:$D)-1,0)
The data validation cells I set to be of type 'list', and as the formula I just refer to this named range:
=Categories
The effect of this is that these cells now have a data validation drop down containing the categories. And when updating the category list, the data validation drop downs update accordingly.
However, today I found out that in Excel 2019 the drop down list behaves as usual (showing the current options), but users are now capable of typing in just about anything in those cells without Excel raising the "invalid data" prompt. This used to work just fine IIRC ever since I started using this technique (before Excel 2010), with Excel giving the users a slap on the wrists when they manually typed non-conforming data. But when I rechecked today in Excel 2019 and even in Excel 2010, it now allows invalid user-typed data without giving any prompt whatsoever.
Looking what actually got broken, I first simplified the formula in the 'Categories' named range to just refer to a fixed range (e.g. Config!D3:D11), but that didn't fix anything; the user is still able to type in anything he wants. I then replaced the formula in the data validation cells with
=indirect("Config!D3:D11")
and that makes Excel recognize invalid user-typed data again. However, by using the indirect formula I can't refer to named ranges, let alone use a formula. And indirect won't update the cell addresses when users move the list source cells around. And using these dynamic-list formulas directly in the data validation formula isn't allowed either.
So is this a genuine Excel bug, introduced somewhere in the last few years, or was this functionality always working this way and was I just not aware of it all this time?
Update
As the answer by @Ghislain said, it is blanks in the option list that causes Excel to suddenly accept anything the user types. And it appears the checkbox "Ignore blanks" controls this behavior.
I intuitively always thought the option meant something like "Allow users to keep the cell blank" or such. Not to lose my sanity, I also checked what Microsoft themselves think it does. Quote from the Excel 2019 help page for the dialog:
Select the Ignore blank checkbox if you want to ignore blank spaces.
And quote from the Excel 2010 offline chm help file:
If it’s OK for people to leave the cell empty, check the Ignore blank box.
So it seems they themselves also haven't sorted it out quite yet :)
