Data Validation: Ignore Blanks

[jstevens]

I thought by checking the box to "ignore blanks", the blank cells in the range would not appear in the dropdown list.

untitled.png

Am I missing something? My goal is not to have the blanks appear based on the assumption that the "data validation list" will always have blank cells. I have no control over the list nor the capability of sorting it. These are the parameters that I have to work with.

Regards,
John

[Rudi]

See this page for explanation: http://www.ozgrid.com/Excel/data-validation.htm

From the page:
Ignore Blanks: Meaning if you referenced, say A1:A10 as your "Source" for the list and only A1:A5 had entries, the blank cells would not be shown until such time as they have data in them. Very handy! In most cases this would be left ticked (default)

This validation feature cannot be controlled much. It's a convenience feature. There is not much you can do if you cannot get access to the original source. The only other way is using an ActiveX control and macros to organise the entires of the list, but this too is not the best option for what you intend.

[HansV]

The description on Ozgrid is incorrect. "Ignore blank" is a misleading description and it only works in a very specific context: if the source range of the validation is a named range and if that range contains blank cells, users can enter any value if "Ignore blank" is on. If "Ignore blank" is off, the user can only enter values from the list (including a blank).

[jstevens]

Hans,

Since I have "Ignore blank" checked, why am I able to select a blank from the dropdown? I thought it would not allow me to select blanks.

Regards,
John

[HansV]

You can always clear a cell, and if the dropdown list contains a blank, you can always select it, regardless of the settings. The "Ignore blank" check box has the rather obscure function displayed in my previous reply.