Data Validation Not Working

B

Brett

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?
 
B

Brett

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?
 
B

Brett

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.
 
B

Brett

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
 
D

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.
 
Top