Collecting Data from a weekly schedule and displaying it on a daily schedule

Hello,

I have searched many different discussion boards and have not been able to find a clear answer to my problem so I am hoping I can explain it well enough to get some help here.

I have a workbook that contains a schedule of shift workers with each worksheet displaying two weeks at a time. The first column contains the name of each employee, the second column contains their pay grade and the third column contains their assigned role for that two week period. The fourth column identifies their assigned day or night shift team. The following 14 columns go from Sunday to Saturday and contain specific text that indicates whether they will work and 12 hour or an 8 hour shift and whether it is day or night shift.

I have a separate workbook with each worksheet breaking down each day with a list of who is working, the role they will fill and the hours of their shift. I would like for this workbook to look at the first one, find the column of the day it represents within the two-week worksheets, identify the shifts that within that column and then list the names and their assigned role. I used an INDEX and MATCH formula to search columns 1,2, & 3 for the assigned role and to then give me the name of the person that is assigned but I don't know how to add the additional criteria that would list that person and their role only if they are actually scheduled for the specific day.

I hope this makes sense. All help is greatly appreciated and let me know if you need further clarification and I will do my best to make things more clear.


In the sample file, I have included the weekly schedule and daily schedule in one workbook but in reality these will be two separate workbooks. Thanks so much for the help!