Data Validation Not Working

[Guest]

Is there a problem with Data Validation using Defined Names? I created a
Defined Name and then used it as a Validation Criteria Formula, but it didn't
work. Then I tried troubleshooting the problem by entering the Defined Name's
formula instead, and it worked?

 

[Debra Dalgleish]

In the Source box, type an equal sign, then the range name, e.g.: =MonthList

There are instructions and examples here:

http://www.contextures.com/xlDataVal01.html

 

[Guest]

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]

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

 

[Guest]

Well it didn't work in my spreadsheet. I can try to enter the exact same
thing, first time with the box checked and it doesn't work, the second time
with the box unchecked and it does work.

 

[Guest]

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]

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.