Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Attn John Peltier
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Attn John Peltier
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/P...cle.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/ _______ "Steve" wrote in message link.net... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
If / Lookup / Filter type formula ,..? | Excel Discussion (Misc queries) | |||
VLookup & HLookup | Excel Discussion (Misc queries) | |||
Excel - columns into rows | Excel Discussion (Misc queries) | |||
Excel - Columns into rows | New Users to Excel |