Create a weekly course schedule

Hi excel experts!

I want to create a schedule that shows our undergraduate and graduate courses by day over the week to identify the scheduling overlaps.

Is there a way to have a workbook with one tab listing the courses, days, times, course code and name, and prof. And a second tab with the days of the week and hours with courses showing by day?

I found this thread about autofilling a calendar (excel-general/1101230-how-to-build-auto-fill-calendar-on-excel.html) which is close to what I want, but I don't need the full month, only 1 week.

Needs:
- time duration...maybe replacing the 1 - 10 on the left hand side?
- colour coding to distinguish between undergrad and grad
- courses listed on the same day at same time or overlapping time to somehow show side by side
- ability to filter based on criteria in columns on source tab (ie. filter by Session, Course code, instructor...etc)

Below is a mock up of an idea but I don't know how to get the week to populate automatically. I have about 110 courses so I expect the schedule tab to be a big worksheet. I'm also open to only having the course code listed on the day and time.

Any advice or suggestions appreciated.

Thanks!
Mike

P.s. thanks alansidman!

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Hello Mikeuoft and Welcome to Excel Forum.
Not exactly what you had mocked up, however perhaps close enough to qualify as "Any advice or suggestions".
Some changes need to be made to the Course list page.
1. Each record is assigned to a single day, i.e. no Wednesday Thursday in the same cell
2. Separate start and end time
3. [optional] Convert range to a table so the formula on the Schedule sheet will update automatically as rows are added/deleted.
The formula for the Schedule sheet is:

Formula:

=IFERROR(IFERROR(INDEX('course list'!$C$2:$C$9&'course list'!$D$2:$D$9&CHAR(10)&'course list'!$F$2:$F$9&CHAR(10)&'course list'!$J$2:$J$9&" "&'course list'!$K$2:$K$9,AGGREGATE(15,6,(ROW('course list'!$D$2:$D$9)-ROW('course list'!$D$1))/('course list'!$G$2:$G$9=B$1)/('course list'!$H$2:$H$9=$A2),COUNTIFS($B$1:B$1,B$1))),INDEX('course list'!$B$2:$B$9,AGGREGATE(15,6,(ROW('course list'!$D$2:$D$9)-ROW('course list'!$D$1))/('course list'!$G$2:$G$9=B$1)/('course list'!$I$2:$I$9=$A2),COUNTIFS($B$1:B$1,B$1)))),"")

Conditional formatting is applied using:
For graduate courses: =ISNUMBER(SEARCH("Grad",B2))
I could not think of a way to turn the whole block green, perhaps someone else can help there.
For other courses: =OR(B2<>"",ISEVEN(SUMPRODUCT(--(B$1:B1<>""))))
Note that after new courses are added on the course list sheet you may need to use AutoFit Row Height (under Format on the Home tab)
Let us know if you have any questions.