Room Placement Grid

[Ange Kappas]

Hi,
I've been trying to chart colors into cells according to if a room
is occupied for a certain period according to the period of stay of a guest.
There is the worksheet which reperesents room numbers
vertically and the dates horizontally:

A B C D E
F G
1 Date 4-5-08 5-5-08 6-5-08
7-5-08 8-5-08 etc
2 Room No
3 101
4 102
5 103
etc

Then I have a table where I have the name of the guest, room number and
period of stay.(Arrival Date and Departure Date)

Room No. Name Arrival Date
Departure Date
101 Smith 4-5-08
7-5-08
103 Jones 6-5-08
8-5-08



When I open the worksheet I want it to go to the table looking at the period
of stay of the guests and to plot on the worksheet lets say a color which
represents the room which is occupied on those days, along with the name of
the guests.
A B C D E
F G
1 Date 4-5-08 5-5-08 6-5-08
7-5-08 8-5-08etc
2 Room No
3 101 Smith Smith Smith
Smith
4 102
5 103
Jones Jones Jones
etc

Of the above it would be simpler on the cells to represent a color lets say
from C3 to F3 with the name in the first cell C3.

Any help would be appreciated.
Thanks
Ange

 

[Bob Phillips]

Ange,

First create a named range to cover the room data in the four columns A-D
(Insert>Name>Define.., Name of RoomData, Refersto of $A:$D)

Then, assuming that there are real dates in the grid in B3, C3 etc. (if not,
create the actual dates as format them as just d), use conditional
formatting.

Select the area of the grid to be highlighted, starting in B4
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add this formula
=SUM(($A4=INDEX(RoomData,0,1))*(B$3>=INDEX(RoomData,0,3))*(B$3<=INDEX(RoomData,0,4)))
Go to thePatterns tab
select a good colour
OK out

That should do it.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)