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

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" wrote
in message ...

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

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


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

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" wrote
in message ...

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