Data Validation

**Gos-C** · 08-07-2006, 08:41 AM

Hi,

Okay, let me try to simplify my problem.

Cell CN2 is formatted as d and contains the formula:
=DATE(2006,7+1,0)

Cell CP2 is also formatted as d and contains the formula:
=IF(CN2=DATE(2006,7+1,0),"",DATE(2006,7+1,0))

CK5 is the input cell and needs to be restricted to accept only the number in CN2 - if CP2 is blank, or a numbers greater than or equal to the number in CN2 and less than or equal to the number in CP2 - if CP2 is not blank.

So I set the Validation citeria to Custom, and try to entered the following formula:

=OR(AND(CK5>=DAY($CN$2),CK5<=DAY($CP$2)),AND(CK5=DAY($CN$2),$CP$2 =""))

I get the message "The formula currently evaluates to an error. Do you which to continue?"

When I choose "Yes," it does not accept 31 in cell CK5, (but when I delete the formula in cell CP2 - leaving the cell blank, it accepts 31 only)

I know that the problem is being caused by the blank ("") in cell CP2 but I can't figure out how to fix it.

Any help?

Thanks,
Gos-C

**SteveG** · 08-07-2006, 10:28 AM

Gos-C,

In the Data validation option select Whole Number and between.

In the Minimum enter =DAY(CN2)

In the Maximum enter =IF(CP2<>"",DAY(CP2),DAY(CN2))

This sets the minimum to whatever is in CN2 and the maximum to CN2 if CP2 is blank and CP2 if CP2 is not blank.

HTH

Steve