Custom Validation Criteria Problem

[pvcc]

I am trying to apply a custom validation criteria formula which allows a value in column B between 0 and 15 as long as there is no value in the corresponding cell in column C

In the custom formula I have
=AND(B2>=0,B2<=15,C2=””)
when I enter the value 25 in B2 and C2 is empty the value is accepted it should display an error message.


If I try =AND(B2>=0,B2<=15) in the customer formula
when I enter the value 25 in B2 the error message is displayed

If I try = C2=”” in the custom formula
when I enter any value in B2 and there is a value in C2 the error message is displayed

Any suggestions on entering the first custom formula

Thanks

 

[Gavona]

=AND(B2>=0,B2<=15,ISBLANK(C2))


Gavin

 

[pvcc]

Gavin,

I tried
=AND(B2>=0,B2<=15,ISBLANK(C2))
and still do not get an error message when I enter a value such as 25 in cell B2 and C2 is blank.

I also tried
=AND(B2>=0,B2<=15,LEN(C2)=0)
and again do not get an error message when I enter a value such as 25 in cell B2 and C2 is blank.

any other suggestions

 

[Gavona]

Works for me.....got it!
In the datavalidation screen untick "ignore blank"





Gavin

 

[pvcc]

Gavin, I overlooked the ignore blank checkbox. Once I removed the check the data validation worked as expected. Thanks for your help.