Data Validation Not Working

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

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.

"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
>
>

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

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