Room Occupied Checker Formula Help

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!

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

=SUMPRODUCT(--(C$2:C2=C3),--(ISNUMBER(D$22)))=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!

Shorter formula:

=NOT(SUMPRODUCT(--(C$2:C2=C3),--(ISBLANK(D$22))))
--
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$22)))=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!

Hmmm. Well I'm getting the error but even when there's not one duplicate
value in the sheet, and wether or not there's an ExitDate. Let me be a
little more specific. I thought I could just change the letter values to fit
my sheet because it's more complicated than I led on. The sheet has over
2500 entries in it so far. The column for the Room number is actually G and
the EndDate column is actually L. I'm starting the validation at row 2626
(because I don't really care about everybody before them who all ready have
exit dates. So, here's how I modified the formula:

I selected cell G2627, then selected Data Validation, Custom and entered:
=NOT(SUMPRODUCT(--(G$2:G2626=G2627),--(ISBLANK(L$2:L2626))))
I enter my error message, select OK, then use the pull down handle to copy
and paste the validation through the rest of the sheet down through row 4000.


However this doesn't seem to work. I can enter the same room number wether
or not there's an end date or not. Did I do it wrong or am I missing
something?
--
I would give my left hand to be ambidextrous!


"Luke M" wrote:

Shorter formula:

=NOT(SUMPRODUCT(--(C$2:C2=C3),--(ISBLANK(D$22))))
--
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$22)))=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!

I meant to say I'm NOT getting the error. I can put the same room number all
over the place without it throwing the error. (sorry for the confusion.)
--
I would give my left hand to be ambidextrous!


"Dax Arroway" wrote:

Hmmm. Well I'm getting the error but even when there's not one duplicate
value in the sheet, and wether or not there's an ExitDate. Let me be a
little more specific. I thought I could just change the letter values to fit
my sheet because it's more complicated than I led on. The sheet has over
2500 entries in it so far. The column for the Room number is actually G and
the EndDate column is actually L. I'm starting the validation at row 2626
(because I don't really care about everybody before them who all ready have
exit dates. So, here's how I modified the formula:

I selected cell G2627, then selected Data Validation, Custom and entered:
=NOT(SUMPRODUCT(--(G$2:G2626=G2627),--(ISBLANK(L$2:L2626))))
I enter my error message, select OK, then use the pull down handle to copy
and paste the validation through the rest of the sheet down through row 4000.


However this doesn't seem to work. I can enter the same room number wether
or not there's an end date or not. Did I do it wrong or am I missing
something?
--
I would give my left hand to be ambidextrous!


"Luke M" wrote:

Shorter formula:

=NOT(SUMPRODUCT(--(C$2:C2=C3),--(ISBLANK(D$22))))
--
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$22)))=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!

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$22))))
--
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$22)))=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!