If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I create a schedule from a list of dates ? | Gavin Morris | Charts and Charting | 1 | June 22nd, 2005 12:24 PM |
Contact full name items order | Alexander | Contacts | 9 | June 22nd, 2005 10:59 AM |
Desktop ICONS disappeared/changed | John Blaustein | Setup, Installing & Configuration | 26 | June 13th, 2005 08:56 PM |
Attn: John Vinson See JoanOC 10/8:Re Two Tables Problem | JoanOC | Running & Setting Up Queries | 0 | December 20th, 2004 05:11 PM |
Split combined name (Smith, John) into (Smith) (John)? | KCAtkins | Running & Setting Up Queries | 6 | November 15th, 2004 09:42 AM |