Complex Calendar Start Date Populates Calendar with Courses

[nicolebernd]

Hello Excel Masters!

I have a pretty complex calendar spreadsheet that I use to schedule my college's student course schedule. Unlike traditional colleges, once enrolled, the student has a set course schedule from their start date to their graduation date.

However, I was curious if it would be possible to populate the course information into an excel calendar based on a start date I enter and can change at any time. 

So far here is how I think I could set it up:

1. Have a separate sheet acting as a master list for the course schedule. 

2. Have a field in my calendar spreadsheet that I can edit the start date for a certain group.

3. When I put a date in that field on the calendar spreadsheet it will automatically pull the data from the master list and populate it in succession in each field on the calendar.

I know how to reference other sheets and pull data, but I am stuck on how to make the date refer to a specific cell that would hold the date data. Then how do I make the other data populate consecutively from there? 

In the attached spreadsheet you will see that I am manually using the "Paste as link" for each calendar field. However, this has led to a lot of triple checking, wasted time, and makes it difficult when I am trying to figure out what start dates align best with the other courses in process. It would be nice to change the entire chain using a start date and have it automatically populate. 

Perhaps something with Unix epoch time? Anyone have any ideas or done something similar? I am stuck on this one!

[#2]

Hello @nicolebernd 

Few questions, just to be clear. Let us take example of "DEC 20" Calendar sheet. when you say
"how to make the date refer to a specific cell that would hold the date data. Then how do I make the other data populate consecutively from there?"
1.Does it mean that you have to get cell C4:C10 from masterlist based on the date field in D3,E3 (Nov 29). Similarly the rest of the dates from Dec 1 till Jn 2?
2. How will the calendar know that it is break
3. what do “A PM, B AM” etc mean (present in columns A and B)?
4. is the calendar specific for Semester?

[#3]

@Kodipady Thank you for helping me have a look at this!

To clarify, the B AM, A PM Labels are for the different groups of students. I have more than one group going at any given period so this calendar represents a master course schedule for all student groups in the program.

The row following the "B AM" and "A PM" shows that the group of students scheduled classes for that week. My goal would be to have the rows that follow "A PM" and "B AM" auto-populate the course progression as outlined in the reference excel spreadsheet called "MasterClassList" based on a start date I specify in another cell. If I want a new "B AM" group to start on 8/1/2021 I could put that date in a cell that then forces the pre-set course schedule on "MasterClassList" to populate in the rows for each calendar day that follows until the end of the 89-week program.

Currently, I am manually pasting each week as a link to the "MasterClassList". However, this makes it difficult to play with different start dates. If I want to see how a new start date aligns in the calendar I have to delete anything in those rows and re-paste the entire 89 weeks with the new start date.

The calendar knows it is a break because it follows the natural course progression as outlined in "MasterClassList". The calendar is for all five semesters. The course schedule for all students is the same. They all take the same courses in the same order no matter what start date they have.

What I ask may not be possible but I wanted to see if anyone had any ideas. I am willing to change the format of the spreadsheet if needed to accommodate this function.