Schedule based on Room Assignments for different instructors at different times of day

Hey there,

I'm trying to my a schedule through excel that is first based on Room Assignments for different instructors during different times of the day. Here is my idea I have for what the first Spreadsheet might look like which is a part of a template on Excel. Idea.PNG

Then from there I want to different spreadsheet fill out the Room Schedule Automatically. Like this-Idea 2.PNG

The form for my first sheet works but the second one does work. I can't figure out the issue.

1st Formula works- =IFERROR(INDEX(ClassList,MATCH(SUMPRODUCT((ClassList[DAY]=ClassSchedule[[#Headers],[MW]])*(ROUNDDOWN($B4,10)>=ROUNDDOWN(ClassList[START TIME],10))*($B4<=ClassList[END TIME]),ClassList[UNIQUE]),ClassList[UNIQUE],0),2),0)

2nd Formula doesn't work for my second sheet- =IFERROR(INDEX(ClassList5,MATCH(SUMPRODUCT((ClassList5[DAY]=ClassSchedule3[[#Headers],[MW]])*(ROUNDDOWN($B6,10)>=ROUNDDOWN(ClassList5[START TIME],10))*($B6<=ClassList5[END TIME]),ClassList5[UNIQUE]),ClassList5[UNIQUE],0),2),0)


From there I would like to extract the based on name of the instructor onto another spreadsheet where it shows all of their individual classes listed.

Lastly, One one spreadsheet showing the data based on when each instructor is in class.

I really don't know if this is asking a whole lot, but I figured I would ask and see if I could get some help with it. If there is an easier way to do all of it then I'm all ears. Any help what be appreciated.

Thank you for your time!
Jon

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you today.)

I believe that I have fixed the formula on the 348 sheet.
I also produced a new table on the 346 sheet based on J2:P10 on the Room Assignments sheet which I think would be easier to work with if there are going to be many rooms.
The formula that fills the new table on the 346 sheet is:

Formula:

Please Login or Register  to view this content.

Note that to see the assignments for room 348 change the room number in cell L1 from 346 to 348.
On the Instructor Schedule sheet two helper rows (which may be hidden for aesthetic purposes) are added.
Row 1 is populated using: =IF(F4<>"",F4,E1)
Row 2 is populated using: =TIMEVALUE(LEFT(F5,SEARCH("-",F5)-3)&" "&MID(F5,SEARCH(":",F5)+3,2))
The table in cells F6:AD11 is populated using:

Formula:

Please Login or Register  to view this content.

I did a little work on the Load Analysis sheet but not sure if I made any progress.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.