Attn John Peltier

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hello Jon,

I saw your post about Gantt Charts on February 10. Do you have any
references (Urls) or can you give some guidance for setting up a Gantt Chart
schedule for five rooms where the rooms may be reserved multiple times for
say 1 to 5 days over a month period. A month for each room may have 10 to 20
different reservations and there may be days where the room is not reserved.
Where a reservation starts on the same day the previous reservation ends,
the bars for the room would need to be a different color to distinguish
where the latter reservation starts.

Thanks,

Steve
 
Hi Steve -

To accommodate multiple reservations across the time scale of the chart, I
would use a worksheet-based Gantt chart, which uses conditional formatting
(CF) to turn the cells different colors. This technique is the first one
covered in this article:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

You may run up against the limit of 256 columns. I guess 22 workdays times 9
hour-long blocks a day (counting lunch) is only 200 columns, so maybe you're
okay.

As part of the algorithm for CF, I'd use an alternating color scheme, based
on MOD(<blah>,2), so you can distinguish consecutive reservations.

I assume the reservations are in some kind of list. One challenge is getting
the list information into a form that can drive the CF, complete with
labels. In the back of my mind I'm thinking of two approaches, pivot tables
and VBA. I think ultimately VBA would win out, but I think it'd be worth
spending an hour or so to see what pivot tables could do. What I'm thinking
is generating a table that has 0 (unreserved), 1, or 2; this table is not
the chart itself, but the chart references it to define the CF. The same or
an additional table could be used to generate labels for the chart.

I'm assuming this is going to be a linear chart, with all hours for all days
lined up in one row per room. But this leads to a long thin chart. Maybe a
grid of several rows per room would be better, each row representing a time
period, and each day in a new column.

So many possibilities....

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top