Data Validation

[Gos-C]

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[/B] IS THE INPUT CELL AND NEEDS TO BE RESTRICTED TO ACCEPT *ONL
THE NUMBER IN CN2 - IF CP2 IS BLANK*, OR *A NUMBERS GREATER THAN O
EQUAL TO THE NUMBER IN CN2 AND LESS THAN OR EQUAL TO THE NUMBER IN CP
- IF CP2 IS NOT BLANK*.

SO I SET THE *VALIDATION CITERIA* TO *CUSTOM*, AND TRY TO ENTERED TH
FOLLOWING FORMULA:

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

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

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

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

Any help?

Thanks,
Gos-

 

[SteveG]

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

 

[Gos-C]

Yes! It worked. Thanks a million, Steve. Appreciate your help.

Gos-

 

[SteveG]

Glad I could help and thanks for the feedback.

Steve