Prev: Help with Value in one column...
Next: sorting in Excel 2007 problem
From: Lily on 26 Mar 2010 18:20
Hi, Thanks in advance for your help.

I am creating a calendar to see time availability from a huge table that
list the room, the date, the day, and the time. We rent our facilities, so I
need to know which events are ours and which one are for outsider.
PROBLEM: I found out that if there is an event planned at the same time in
the same day, the formula retrieves a blank space, so it shows as available.
I'm awared that this is a problem of my database, but is there a way than
once the criteria finds one slot occupied, ignore the other events duplicated?

This is the expression (Sorry!):

=IF(SUMPRODUCT(--(Sheet1!$A$2:$A$2500=Room),--(BegDate>=Sheet1!$C$2:$C$2500),--(EndDate<=Sheet1!$D$2:$D$2500),--(Day=Sheet1!$E$2:$E$2500))),--(Time=Sheet1!$G$2:$G$2500),--(Sheet1!$I$2:$I$2500))=1,"Company",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$2500=Room),--(BegDate>=Sheet1!$C$2:$C$2500),--(EndDate<=Sheet1!$D$2:$D$2500),--(Day=Sheet1!$E$2:$E$2500))),--(Time=Sheet1!$G$2:$G$2500),--(Sheet1!$I$2:$I$2500))=2,"Outside",""))

My calendar looks like this:
"Room" 4/5 4/6 4/7 4/8
4/9.............
M T W H
F................
Morning
Afternoon


THANKS MUCH!
From: T. Valko on 27 Mar 2010 00:46
>This is the expression

I'm not sure what you're asking but that formula can be reduced a bit. It
looks like all you're interested in is a result of either 1 or 2. Anything
else returns a blank.

You don't need to use both SUMPRODUCTs.

All on one line:

=LOOKUP(SUMPRODUCT(
--(Sheet1!$A$2:$A$2500=Room),
--(BegDate>=Sheet1!$C$2:$C$2500),
--(EndDate<=Sheet1!$D$2:$D$2500),
--(Day=Sheet1!$E$2:$E$2500),
--(Time=Sheet1!$G$2:$G$2500),
Sheet1!$I$2:$I$2500),{0;1;2;3},
{"";"Company";"Outside";""})

--
Biff
Microsoft Excel MVP


"Lily" <Lily(a)discussions.microsoft.com> wrote in message
news:80D80A5A-CABB-4C3F-8250-3D0DD8A82E85(a)microsoft.com...
> Hi, Thanks in advance for your help.
>
> I am creating a calendar to see time availability from a huge table that
> list the room, the date, the day, and the time. We rent our facilities, so
> I
> need to know which events are ours and which one are for outsider.
> PROBLEM: I found out that if there is an event planned at the same time in
> the same day, the formula retrieves a blank space, so it shows as
> available.
> I'm awared that this is a problem of my database, but is there a way than
> once the criteria finds one slot occupied, ignore the other events
> duplicated?
>
> This is the expression (Sorry!):
>
> =IF(SUMPRODUCT(--(Sheet1!$A$2:$A$2500=Room),--(BegDate>=Sheet1!$C$2:$C$2500),--(EndDate<=Sheet1!$D$2:$D$2500),--(Day=Sheet1!$E$2:$E$2500))),--(Time=Sheet1!$G$2:$G$2500),--(Sheet1!$I$2:$I$2500))=1,"Company",IF(SUMPRODUCT(--(Sheet1!$A$2:$A$2500=Room),--(BegDate>=Sheet1!$C$2:$C$2500),--(EndDate<=Sheet1!$D$2:$D$2500),--(Day=Sheet1!$E$2:$E$2500))),--(Time=Sheet1!$G$2:$G$2500),--(Sheet1!$I$2:$I$2500))=2,"Outside",""))
>
> My calendar looks like this:
> "Room" 4/5 4/6 4/7 4/8
> 4/9.............
> M T W H
> F................
> Morning
> Afternoon
>
>
> THANKS MUCH!


 | 
Pages: 1
Prev: Help with Value in one column...
Next: sorting in Excel 2007 problem