IF function for scheduling tool with outlook integration

**systematic** · 12-16-2005, 07:13 AM

Hi all,

I'm in desperate need of the help of one of you resident genius'. I'm not quite sure how to approach this one.

I'm trying to build a very simple scheduling tool to improve the decision making process behind the booking of meetings in an environment that houses 80+ staff members.

All bookings are made into the calendar, then the calendar info is exported to an excel file. The excel file has the raw calendar data (Sheet 2) and an overview (Sheet 1).

Sheet 1 will look like a matrix. Basically, timeslots and dates will be in rows, resources in columns. In it's most simple form, if the resource is booked a '1' or other value is placed into the corresponding cell on the grid.

So cell C2, may be 01/01/06 8:00am (row), Resource 1 (column)
cell C3 01/01/06 8:15am, Resource 1
cell D2 01/01/06 8:00am. Resource 2...........and so on.

Where A contains the date and B contains the time.

I'm not sure if the right approach is to develop some scripting to perform the calculation on whether the staff member is booked, or use a worksheet function. I'm hoping someone can help me out by either pointing me in the right direction or providing me with a formula that may get me started.

Going back to the raw outlook data in excel (Sheet 2), lets say column A is date, B is start time, C is finish time and D is resource name.

In english....I think it needs to read (for cell C2 on Sheet 1)

If 'Sheet 2, Date Column, Cell' is equal to 'Sheet 1, A2 (Date)
AND
If 'Sheet 1, B2 (Time)' is between 'Sheet 2, Start Time' and 'Sheet 2, Finish Time'
AND
If 'Sheet 2, Resource' = 'Sheet 1, C1 (Resource)'
THEN
C2 = 1
ELSE
C2 = 0

For the life of me, I cannot seem to work my way through this challenge.

I also think there may be a problem with using a cell range in the formula (as it will need to check all the cells in the raw data). I'm thinking without using VBA - I may need another worksheet to help reach the end result but not sure how.

If anyone can offer any pearls of wisdom to overcome this challenge......I would be extremely grateful!

Thank you

Rob

Max · 12-16-2005, 09:55 AM

In Sheet1,

Put in C2, array-enter the formula (press CTRL+SHIFT+ENTER):
= --ISNUMBER(MATCH(1,(Sheet2!$A$2:$A$10=$A2)*(Sheet2!$B$2:$B$10<=$B2)*(Sheet
2!$C$2:$C$10>=$B2)*(Sheet2!$D$2:$D$10=C$1),0))

Copy across & fill down to populate the matrix

Adjust the ranges to suit the extent of your data in Sheet2
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"systematic" <systematic.204qyz_1134731702.2844@excelforum-nospam.com> wrote
in message news:systematic.204qyz_1134731702.2844@excelforum-nospam.com...
>
> Hi all,
>
> I'm in desperate need of the help of one of you resident genius'. I'm
> not quite sure how to approach this one.
>
> I'm trying to build a very simple scheduling tool to improve the
> decision making process behind the booking of meetings in an
> environment that houses 80+ staff members.
>
> All bookings are made into the calendar, then the calendar info is
> exported to an excel file. The excel file has the raw calendar data
> (Sheet 2) and an overview (Sheet 1).
>
> Sheet 1 will look like a matrix. Basically, timeslots and dates will be
> in rows, resources in columns. In it's most simple form, if the resource
> is booked a '1' or other value is placed into the corresponding cell on
> the grid.
>
> So cell C2, may be 01/01/06 8:00am (row), Resource 1 (column)
> cell C3 01/01/06 8:15am, Resource 1
> cell D2 01/01/06 8:00am. Resource 2...........and so on.
>
> Where A contains the date and B contains the time.
>
> I'm not sure if the right approach is to develop some scripting to
> perform the calculation on whether the staff member is booked, or use a
> worksheet function. I'm hoping someone can help me out by either
> pointing me in the right direction or providing me with a formula that
> may get me started.
>
> Going back to the raw outlook data in excel (Sheet 2), lets say column
> A is date, B is start time, C is finish time and D is resource name.
>
> In english....I think it needs to read (for cell C2 on Sheet 1)
>
> If 'Sheet 2, Date Column, Cell' is equal to 'Sheet 1, A2 (Date)
> AND
> If 'Sheet 1, A2 (Time)' is between 'Sheet 2, Start Time' and 'Sheet 2,
> Finish Time'
> AND
> If 'Sheet 2, Resource' = 'Sheet 1, C1 (Resource)'
> THEN
> C2 = 1
> ELSE
> C2 = 0
>
> For the life of me, I cannot seem to work my way through this
> challenge.
>
> I also think there may be a problem with using a cell range in the
> formula (as it will need to check all the cells in the raw data). I'm
> thinking without using VBA - I may need another worksheet to help reach
> the end result but not sure how.
>
> If anyone can offer any pearls of wisdom to overcome this
> challenge......I would be extremely grateful!
>
> Thank you

>
> Rob
>
>
> --
> systematic
> ------------------------------------------------------------------------
> systematic's Profile:
http://www.excelforum.com/member.php...o&userid=25294
> View this thread: http://www.excelforum.com/showthread...hreadid=494087
>

**systematic** · 12-16-2005, 08:16 PM

Max - You are a legend!

Thanks so much for your help. This works perfectly - and also saved me a weekend at home trying to figure it all out!

I really can't thank you enough

Regards

Rob

Max · 12-16-2005, 09:10 PM

You're welcome, Rob ! Glad it helped.
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"systematic" <systematic.205rba_1134778801.853@excelforum-nospam.com> wrote
in message news:systematic.205rba_1134778801.853@excelforum-nospam.com...
>
> Max - You are a legend!
>
> Thanks so much for your help. This works perfectly - and also saved me
> a weekend at home trying to figure it all out!
>
> I really can't thank you enough

>
> Regards
>
> Rob