"Ignore Blank" ignored while validating

[AA Arens]

I have defind a list via Insert > Name. If I validate a cell via Data >
Validation and choose list, list name, I still have emty value in the
combo box while the toggle "Ignore Empty" was active.

Bart
Excel 2003

 

[Bob Phillips]

Uncheck it, then blanks are not allowed to be input.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[damorrison]

you could run a little macro like this to sort your range

Sub Macro1()
Range("list2").Sort Key1:=Range("H1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


dropdown menu works best with a sorted list

 

[Dave Peterson]

You can always use edit|clearcontents to empty a cell with data|validation.

This describes what that option is used for--it's taken from VBA's help (not
excel's):

If the IgnoreBlank property is True, cell data is considered valid if the cell
is blank, or if a cell referenced by either the MinVal or MaxVal property is
blank.

===
So if you turn on the formula auditing toolbar and click on the "circle invalid
data" icon, you'll see a circle based on that setting.


And this is from excel's help:

Note If your allowed values are based on a cell range with a defined name, and
there is a blank cell anywhere in the range, setting the Ignore blank check box
allows any values to be entered in the validated cell.

This is also true for any cells referenced by validation formulas: if any
referenced cell is blank, setting the Ignore blank check box allows any values
to be entered in the validated cell.