I'm creating a rotating list of names for multiple different tasks. The below formula works as it should but does not take into account the names next to it on other tasks for that day. On the same day I do not want the same people to be on duty. Is there a way to incorporate a Index/Match function into this so it will return a offset value that does not match the other names that are being used from their respective lists?
Main rotation formula which works
=OFFSET('Input Data (For Schedule Prep)'!$B$2,MOD((ROW(G3)+J4),COUNTA('Input Data (For Schedule Prep)'!B:B)-1),0)
enter image description here
Second Formula Attempt that works but is finicky/must allow iterations:
=IFNA(IF(OR(C4=I4,B4=O4,B4=U4,B4=AG4,B4=AM4,B4=AS4),INDEX('Input Data (For Schedule Prep)'!$A$2:$A$20,MATCH(1,(OFFSET(C5,7,0)<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(OFFSET(C5,7,0)<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(B4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(I4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(O4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(U4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(AG4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(AM4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(AS4<>'Input Data (For Schedule Prep)'!$A$2:$A$20),0)),B4),B4)
Example.JPG
Bookmarks