Calendar Population... macro?

[emfc00]

All,
First off thanks for all the great ideas in the past. I thought I had some good ideas on how to do my current project, but I'm finding myself at a loss. I would like to be able to populate a calendar with training topics (I'm in the Navy) listed on a long range training plan (LRTP), encompassing the entire year. The problem is this LRTP is based on approved instruction so I have to keep the format of months, x's, etc. I would also like my Training Petty Officers to be able to select what days in a week their people conduct training. So in other words they populate the LRTP with X's under what month something is conducted and in no particular order, other than only occurring on previously selected training days, those items (training topic and number) get populated into the calendar, which is later approved by their leadership. Like I said, I had some good ideas involving MATCH, VLOOKUP and RAND. However I forgot RAND changes every time you open the spreadsheet. I'm at the limits of my knowledge, especially if this requires a MACRO. Any ideas!?

Example posted. I used MS Office 2003. Thanks!

[watersev]

hi emfc00,

questions:

1. What is the expected result for April sheet would be and why?
2. What is the overall result expected: all months schedule?
3. If Monday, Wednesday, Friday are selected how do you spread trainings between them (assume it is 4 for April)

[emfc00]

Watersev,
Answers to your questions (I hope):
1. The result for the April sheet would be that its populated with both Topic Name and Number in accordance with the LRTP. Presenter and integration would be filled in manually. The reason for this is to save time and manhours. As our schedule continuously changes so do our needs and abilities for training. If I can save my 58 training petty officers an hour or more a week then that is a lot of time I can use them for something else.
2. I would like all months scheduled. I didn't include all months due to my limited bandwidth. We submit yearly, quarterly and monthly plans... and often.
3. I would like them spread amongst the first available days. For example if its MWF and they only have four entered on the LRTP then it will only fill (for April) Monday, Wednesday, Friday, Monday. They always fill each available training day so they can adjust the LRTP as necessary if a new training opportunity presents itself due to how the month falls out.

If I missed the mark on your questions please let me know. Thanks!

[emfc00]

Watersev,
Answers to your questions (I hope):
1. The result for the April sheet would be that its populated with both Topic Name and Number in accordance with the LRTP. Presenter and integration would be filled in manually. The reason for this is to save time and manhours. As our schedule continuously changes so do our needs and abilities for training. If I can save my 58 training petty officers an hour or more a week then that is a lot of time I can use them for something else.
2. I would like all months scheduled. I didn't include all months due to my limited bandwidth. We submit yearly, quarterly and monthly plans... and often.
3. I would like them spread amongst the first available days. For example if its MWF and they only have four entered on the LRTP then it will only fill (for April) Monday, Wednesday, Friday, Monday. They always fill each available training day so they can adjust the LRTP as necessary if a new training opportunity presents itself due to how the month falls out.

If I missed the mark on your questions please let me know. Thanks!

[emfc00]

Would it be easier if I adjusted the "month" sheets to be adjusted for "by day" basis? Meaning I could go to April and "mark" in some way which days I want available for training instead of placing a blanket "MWF" or "TR"?

[watersev]

please check attachment, press "Create schedule" button.

You'll need to input year the schedule is created for.

The schedule will consist of 12 months, a sheet for every month. Prior creating sheets the code would delete any sheet named with 3-letter month shortname. For the code to work you need to keep "Template" sheet in the file.

[emfc00]

This looks amazing! I consider my self savvy on Excel but this is a whole other level! I really need to learn to code!! Thanks!!!

I just have one question. Is there a way to make it so cells C3 and B4, E3 and D4 and so on (basically the top of each day), don't clear when being copied from the template? The reason I ask is I would eventually like to add a function that draws from a list (the at least I know how to do) of important days, ie holidays, underway periods etc. I tried with this template then realized there was a function in the code that clears the cell. I thought I had it figured on how to change it, but it didnt work.

Thanks so much for your help! Pure magic!!

[royUK]

You shouldn't bneed VBA to do this. I usually have everything set up as a database then populate a Calendar with formulas. I haven't an example at the moment but I'll try later to add one.

[watersev]

@emfc, will the formula for C3, E3, G3 ... be the same? Does the same apply for B4, D4, F4 ...? If yes, can you specify both formulas?
@Roy, retired???

[royUK]

I have quit admin

[emfc00]

Well B4 would contain:

Please Login or Register  to view this content.

D4 would contain:

Please Login or Register  to view this content.

I will do something similar with C3 and E3 except that I'm unsure of how to base it off a list that contains date ranges:

For example if I put on my events list

Start Date End Date
1/1/12 1/10/12
2/1/12 2/10/12

All dates from 1-10JAN12 will show "U/W" and so will 1-10FEB12.

[watersev]

Does the Events list have regulated data table format or you can change it to your discretion?

[watersev]

can you post a file with events list sample?

[emfc00]

The format can stay the same. But our schedule is extremely fluid so I need to be able to change the data within often.

[watersev]

I've changed the way the date is outputed to the month sheets, it will not disturb your formulas as per your request

[emfc00]

Thanks for all the help! Its looking pretty hot!!