I agree, for data validation to work with your defined formula, you have
to uncheck Ignore blanks. However, it's not a general requirement for
data validation to work, when defined names are used.

Brett wrote:
Try this:
Open a new workbook. With the active cell at A1, create the defined name
Valid_Data =isnumber(A1). Then create Data Validation for A1 as Settings /
Validation criteria / Allow: Custom ; Formula: =Valid_Data.

Now enter a number in A1 - Result: no error - correct.
Now enter a letter in A1 - Result: no error - incorrect

Now go back to the Data Validation dialog box and uncheck 'Ignore blanks'.

Now enter a number in A1 - Result: no error - correct.
Now enter a letter in A1 - Result: error - correct


"Debra Dalgleish" wrote:


Data Validation lists will work, even if 'Ignore blanks' is checked.

Brett wrote:

OK, I figured it out for myself. All I needed to do was uncheck the "Ignore
blanks" check box. For some reason, validation criteria formulas with defined
names won't work unless this is uncheked. Go figure?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html