Populate data to different worksheets

Hello, I am desperately trying to create a formula to populate worksheets that contain calendar templates with data being entered on another (master) worksheet. I also need the formula to populate three other columns on the master worksheet that the data is being entered on. There are some rather complicated stipulations however.

We train 468 new bus drivers every year and to accomplish this, we run 26 classes of 18 students with a new class starting every two weeks at our training facility. Each class is in training for six weeks (30 working days). The first 19 days they are under the instruction of our full-time instructors. The next nine days they are out on ‘line instruction’ with regular drivers. On days 29 and 30 they are back in the training department for final evaluation and graduation.

If I enter a class start date of January 5, 2009 in cell B4 on "2009 Conventional Classes", the formula would automatically fill in the "Eval/Grad" and "Service Delivery" columns with dates that are 29 days later and 31 days later respectively.

At the same time, on the "Jan 09" worksheet, the formula would populate the cell for January 5th with “CL1 (1)” and the cell for January 6th with “CL1 (2)” and so on for 19 weekdays excluding weekends and holidays (different background colors). CL1 (1) means Class 1, Day 1. Because Day 9 is Road Test Day, the result would be slightly different and look like this: “CL1 (9R/T). The formula would also enter “CL1 (Eval)” in the cell for February 12th and “CL1 (Grad)” in the cell for February 13th.

The formula would also calculate the number of instructors required at a rate of one instructor for every two students and put that number in different brackets after i.e. “CL1 (1) [9I]” in cell C5 on worksheet “Jan 09”. The one exception to this is on Day 30, Graduation Day, where only two (2) instructors are required for the whole class of 18.

The formula would also recognize that for the first nine days, the students are driving ‘diesel’ and for the next 10 days ‘trolley’ and put those results in different brackets again so the results would look like this: “CL1 (1) [9I] {9D}” in C5, “CL1 (2) [9I] {9D}” in D5 etc. up to F5 and then beginning with cell G5, the results would look like this: “CL1 (10) [9I] {9T}” up to cell F8 which is January 29th (Day 19) the last day of training with the full-time instructors.

So where this whole thing gets even more complicated is that on January 19th, Class 2 starts with their Day 1 and on February 2nd, Class 3 starts with their Day 1 so you can see that we have all kinds of overlap with classes running at the same time but on different schedules.

I am planning to add the worksheets for the following at a later date: we also have “Community Shuttle”, “Maintenance Forklift” “STARS” and “Trainability” requirements running concurrently and the dates for these classes would be entered on additional worksheets in the workbook with the idea being that they would also automatically populate the monthly worksheets with different data: i.e. Community Shuttle Day 1 would look like “CS1 (1) [2]”, Maintenance Forklift Day 1 would look like “MM1 (1) [2] {2D}”, STARS would look like “STARS (2a) [2]” and Trainability’s would look like “TBTYs [1]”.

Community Shuttle classes are for 8 students and are 8 days in length with no diesel or trolley requirements. Community Shuttle Day 1 only requires 2 instructors with the subsequent 7 days requiring 4 instructors i.e. Day 2 would look like this: “CS1 (2) [4] etc..
Maintenance Forklift classes are for 4 students and are 8 days in length with their Road Test on Day 7 (would look like this: “MM1 (7R/T) [2] {2D}”) and on Day 8 they do their forklift training with one instructor which would look like this: “MM1 (8F/L) [1]”
STARS (Safety Training and Refresher Series) classes are two days in length and require 2 instructors and no buses.
Trainability’s are a half day each where an instructor takes out potential hires (two at a time) to find out if they are trainable (diesels required).

Also, on the “Jan 09” worksheet and all subsequent monthly worksheets, at the bottom of each cell that represents a weekday, there needs to be a running calculation of the total number of instructors, diesels and trolleys that are required for that day. It might look something like this: “Totals: 21 INS 10 DSL 9 TLY”.

Please help!

Bump, for moderator...

No response.

My apologies to this forum and any others I may have offended by cross-posting. No excuse but in my haste to come up with a response to my problem, I neglected to fully understand the rules. I do now.

I politely withdraw my post if that is possible but would sure like to know if anyone can point me to an online commercial excel service that might be willing to undertake this project.

Kindest regards,

leopardhawk