entry must match value in range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JayBro - 23 May 2008 17:07 GMT

I have a range of cells with dates like: 1/1; 1/15; 1/31; 2/1; 2/15; 2/28...

To the left of the range a user will enter a date. I want to make sure that
the date matches a value in the range above. For example, 1/7 is invalid
whereas 2/1 is fine. I'd prefer not to use a dropdown. This hasn't been easy
for me to figure out and I'm hoping someone can help me instert the correct
validate formula.

Reply to this Message

Gary''s Student - 23 May 2008 17:28 GMT

Data > Validation > Custom > Formula:

=COUNTIF(B1:IV1,A1)>0

Signature

Gary''s Student - gsnu200788

Reply to this Message

Rick Rothstein (MVP - VB) - 23 May 2008 17:37 GMT

Is the column you are filling in formatted as Date with a display format of
m/d? If so, you can delete your range of approved dates and use this
Validation formula...

=OR(DAY(A1)=1,DAY(A1)=15,DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))

Note: This formula will use February 29th (instead of the 28th) as the valid
end of February date in a leap year (such as this year).

Rick

>I have a range of cells with dates like: 1/1; 1/15; 1/31; 2/1; 2/15;
>2/28...
[quoted text clipped - 7 lines]
> correct
> validate formula.

Reply to this Message

JayBro - 23 May 2008 19:11 GMT

I'm still not able to make it work. I'm using Excel 2007. Under Data | Data
Validation I'm usign the criteria "Date, "equal to" and entering Rick's
formula. The result is that nothing appears to be valid. I tried the COUNTIF
suggestion too matching to a series of existing dates but get the same
behavior. Can someone steer me in the right direction? Maybe it's back to the
drawing board on Data Validation, I don't know.

> Is the column you are filling in formatted as Date with a display format of
> m/d? If so, you can delete your range of approved dates and use this
[quoted text clipped - 18 lines]
> > correct
> > validate formula.

Reply to this Message

Rick Rothstein (MVP - VB) - 23 May 2008 19:18 GMT

The result from my formula is not a date (it produces a logical TRUE or
FALSE result), so you can't validate it as one. Instead of Date, select
Custom from the Validation Criteria's Allow drop-down field and then put my
formula in the Formula field.

Rick

> I'm still not able to make it work. I'm using Excel 2007. Under Data |
> Data
[quoted text clipped - 31 lines]
>> > correct
>> > validate formula.

Reply to this Message

JayBro - 23 May 2008 20:10 GMT

Success! Thank you, Rick.

> The result from my formula is not a date (it produces a logical TRUE or
> FALSE result), so you can't validate it as one. Instead of Date, select
[quoted text clipped - 38 lines]
> >> > correct
> >> > validate formula.

Reply to this Message