Data Validation
 

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 *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 [b]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


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=568954

Data Validation
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568954

Data Validation
 

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

Gos-C


--
Gos-C


------------------------------------------------------------------------
Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
View this thread: http://www.excelforum.com/showthread...hreadid=568954

Data Validation
 

Glad I could help and thanks for the feedback.

Steve,


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568954