Excel Master Calendar generated from Excel Table

Hi Everyone,

I work for an adult school and I am trying to auto generate a master scheduling calendar based off of data from a separate table. I researched message boards and found a template that will take class start date and class name and add to a calendar but I need to incorporate more info. I need class name, start date, end date (and reoccurring dates), time, and classroom to be pulled and populated into a calendar. Like I said, I am able to get class name and one date but cant seem to take it any further.

Any help would be greatly appreciated!

Thanks,
Jon

Can you share what you have so far, by attaching a workbook to one of your posts? The yellow banner at the top of the screen explains how to do this.

Hope this helps.

Pete

Hi Pete,

I attached a sample with the data I was trying to pull and also a template I found on this forum that got me partially there. Let me know what else you need! thank you.

Jon

That calendar looks like an early version of one of mine, although it will take quite a bit of work to convert it into what you want. As you say, it doesn't take account of the End date, nor of the different days of the week that a course might run on. Some courses have multiple days (are these for different classes ?), but here there are some inconsistencies in how the data is laid out. For example, you have Tues/Wed/Thurs on row 2, but on row 4 you have Tu/Wed, so it might be clearer if you had 6 narrower columns, one for each day of the week, where you just have a "Y" to indicate the day. If these are different classes, though (i.e. with a different set of students in each one), then it would be better to record them as separate records in your table. I think there will also be problems trying to cope with the record on row 38, where you have different times for a course which takes place on Fri/Sat, and again this would be better shown as 2 different entries. There is also a block of online courses which don't have a specific day allocated (and presumably no room either), so should they even be included?

In later versions of the calendar file, I have it set up to generate in a separate table all the dates within the range specified by the start and end dates. This is done automatically from the base data, so it doesn't require any User intervention, but it is this "expanded" table which is used to generate the calendar display.

Another thing to consider is the amount of data to be displayed You have long titles, and you also want to include the times and room allocated (the start and end dates do not need to be shown). You don't seem to have any classes on Sunday, so that column can be hidden on the calendar to make the other columns wider, but even so you may end up with having to use a smaller font to fit everything in, or having 2 lines for each cell in the display. That may also limit the number of simultaneous "events" that you can display on any particular day.

Let me know your thoughts on these points.

Hope this helps.

Pete

Thanks Pete. Yes, I believe the 2nd calendar was a version of yours. I did see your name on some of the early threads I was reviewing. I should have clarified that the sheet wasn't fully finished.
Everything beyond row 26 is incomplete and doesn't need to be addressed at this time. The block of online courses is just a reference to me. They wouldn't need to be in the calendar since they are self-paced online
classes with no start or end date.

I do like/understand the idea of having columns for days of the week and checking off when the classes meet. The majority are weekly classes that just meet every Monday for example. I do have a few classes that meet
more than one time a week (Court Reporting). I can also shorten names no problem. This calendar is more for internal purposes not to be used for the public.

Besides the columns for days of the week what would be the next steps that I need to take?

thanks - Jon

I'll take a look at it tomorrow, as it is getting a bit late here. I'll see if I can amend one of my other Date-range calendars.

Pete