Data Validation Not Working

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?

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

Brett wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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?

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