Call Rotation Help

[aharb]

I'm trying to create a formula/function to replicate a fairly simple call schedule. I've attached a spreadsheet with an example of what I have so far. I've found a way to automate the rotation, but I'm having trouble figuring out how to automate the weekends. Only one person from the two person team should be on call for each weekend. I need it to rotate back and forth each time that team gets a weekend between the two who are on call. Hopefully that makes some sense.

I would appreciate any help. Thanks in advance.

Call Rotation.xlsx

[dredwolf]

Okay these 2 are a bit long, but they do the trick:
E3:

=IF($D3="Sat-Sun",IF(COUNTIFS($D$2:$D2,"Sat-Sun",E$2:E2,E2)<=COUNTIFS($D$2:$D2,"Sat-Sun",F$2:F2,F2),E2,""),INDEX(I$15:I$18,MOD(MATCH(IF(E2<>"",E2,$F2),IF(E2<>"",I$15:I$18,J$15:J$18),0),4)+1))

F3:

=IF($D3="Sat-Sun",IF(COUNTIFS($D$2:$D2,"Sat-Sun",F$2:F2,F2)<COUNTIFS($D$2:$D2,"Sat-Sun",E$2:E2,E2),F2,""),INDEX(J$15:J$18,MOD(MATCH(IF(F2<>"",F2,$E2),IF(F2<>"",J$15:J$18,I$15:I$18),0),4)+1))

Drag both down as far as needed
Note- I revised your rotation formula to one index statement instead of 4 nested IF's

(see attached)

Hope this helps

[aharb]

Thanks. This is perfect. This should save me a ton of time in the future. I like the index method better than my 4 nested IF statements as well. I appreciate your help and time.

[dredwolf]

you are welcome