I am making a roster and schedule for my fire department. I have most of it done and now I'm stumped. I've played around with index and matching, but I haven't been able to make it work for me yet. There might be another way that I don't know of. The reason for needing this is we swap shifts quite often and this will make keeping up with changes much easier.

Page 1 of my workbook, named Roster, has a roster and a number assigned to each person. The number represents that persons regular day off(RDO).


On page 2 of my workbook, named Calendar, I have a list of the people that are supposed to be on duty for each day of the year. What I need is when number "1" shows up on a day it fills in a cell that says shawn is off, when "2" shows up, jim is off etc. And this needs to apply to each shift. And there are two names assigned to each number per shift...so confusing.


I hope my question make sense. I have included an attachment with part of my calendar. The bottom 2 empty cells on the Calendar page need the names associated with the particular "RDO" number. Thanks ahead of time for helping me out.

Attached Files

Calendar Example.xlsx (10.3 KB, 20 views)