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 - 19 Nov 2006 12:16 GMT
Uncheck it, then blanks are not allowed to be input.

Signature
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
> 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
damorrison - 19 Nov 2006 12:19 GMT
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 - 19 Nov 2006 12:23 GMT
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.
> 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

Signature
Dave Peterson