I'm a retard. Never mind! Sorry. It works great! EndDate is in column N
not L. My bad. Luke, you da man! Thanks a million for your help.
--
I would give my left hand to be ambidextrous!


"Luke M" wrote:

Shorter formula:

=NOT(SUMPRODUCT(--(C$2:C2=C3),--(ISBLANK(D$2:D2))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

Select cell C3. Data - Validation, custom. Input:

=SUMPRODUCT(--(C$2:C2=C3),--(ISNUMBER(D$2:D2)))=COUNTIF(C$2:C2,C3)

Setup appropriate messages to be displayed on input/error, as desired.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dax Arroway" wrote:

I have a worksheet with Headers: A:Name, B:StartDate, C:Room#, and D:EndDate.

I'm looking for a formula that will check the availability of a room based
on EndDate. For example if I entered Mr. Smith on one row and gave him room
100 and he hasn't checked out yet (no EndDate) then when I go to the next
line and enter Mr. Black and try to put him into that same room, I'd like to
get an error or warning message saying, "Someone's already in that room!"
but if Mr. Smith had an EndDate, then it wouldn't give me that message.

I hope that makes sense. Basicall I'd like it to check the column for
duplicate entries and if, for those duplicate entries, if there's an EndDate
in the D column. If there's no EndDate, throw error message. If all matches
have EndDates, allow.

Any help???
Thanks in advance!
--Dax
--
I would give my left hand to be ambidextrous!