Excel dynamic data validation + named ranges + invalid typed data — broken, or never worked?

[Carl Colijn]

Hi all,

Note: I already posted this question on Stack Overflow, but I guess that post there isn't going anywhere, so I think it's time for some cross-posting.

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?

 

[Fluff]

Do you have anything in D1 on the config sheet?

 

[Carl Colijn]

Weird: once you think you hit a dead end and ask or further help, suddenly you find the solution. The range indeed contained a blank cell, and while figuring out a workaround I just found out the true meaning of the "Ignore blanks" checkbox in the data validation dialog. All my life I thought it means "Ignore users making the cell blank when there's no blank option in the list", and since this has always been the correct behavior for all Excel solutions I've ever created, and since this option is on by default, I never thought much of it.

Surprise: the checkbox seems to have a drastically different behavior. In reality it means "Ignore the data validation rules whenever there's a blank cell in the list". How unintuitive; who would have thought... I just went to the Excel help page for the data validation dialog (via F1), and there it states:

Select the Ignore blank checkbox if you want to ignore blank spaces.

So they also got it wrong.
Digging into this a bit further, the Excel 2010 local chm file says this about it:

If it’s OK for people to leave the cell empty, check the Ignore blank box.

So @Fluff: you were definitely on the right track

 

[Fluff]

You can either uncheck the checkbox, or just change the -1 in the formula to -2 (or -3) if you have anything in D1

 

[Carl Colijn]

Yeah, that's an option too. But in this particular case the client actually explicitly wants an empty option in the list. Never had a project where that was the case, so I never ran into this issue before. So this time I'll fix it by just unchecking the box.

 

[Fluff]

OK