Dynamic rotating work schedule

[Clooney]

I have a pretty tall order here everyone,

I'm trying to make a rotating sanitation list for the remainder of the year for 3 teams.

I want the sheet to auto assign team members job areas (Rooms;columns B,C,D) based on the days of the week the work.

Members of Team 1 can only be assigned any Room Mon-Fri
Members of Team 4 can only be assigned any Room Sat-Tues and are the preferred team for Sundays
Members of Team 5 can only be assigned any Room Wed-Sat and are the preferred team for Saturdays

Each day in column A will have 3 (columns B-D) different employee ID's in each area(Room)

I want the sheet to keep everyone evenly spread out in terms of occurrences and for everyone to work all 3 sets of rooms.

As an added challenge, i would like to be able to add or subtract employee ID's to and from each team and have the sheet auto adjust.

I have a formula in column B that shows what I am trying to do, but it was from another person's work and I having trouble adapting it to my needs. Basically columns B,C,& D need the formulas.

This is the formula in Cell B3 and I just copied it down

=IF(OR(WEEKDAY($A2)=1,WEEKDAY($A2)=7),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=5,-5,-5),0),Team1,0),$U$2)+1),$G$2),IFERROR(INDEX(Team1,MOD(MATCH(OFFSET(B2,IF(WEEKDAY($A2)=2,-3,-1),0),Team1,0),$U$2)+1),$G$1))

A

B

C

D

F

G

Named

Range

Named

Range

Named

Range

U

V

W

Date

Room 4

Room 5&6

Room 7&8

Gets First Mon-Fri:

LBRMMANI

Team

1

Team

4

Team

5

Team1

Team4

Team5

Sunday, July 01, 2018

LBRRFOXW

Gets First Sat/Sun:

LBRRFOXW

ID

Sundays

ID

Saturdays

ID

9

10

11

Monday, July 02, 2018

LBRBIROB

Mon-Fri

LBRRCARD

Sat-Tues

LBRRBURN

Wed-Sat

LBRPLAYT

Tuesday, July 03, 2018

LBRJOHNS

LBRRFOXW

LBRKSNOW

LBRJCBOU

Wednesday, July 04, 2018

LBRJSMIT

LBRBIROB

LBRSTHOM

LBRMSCRU

Thursday, July 05, 2018

LBRMMANI

LBRJOHNS

LBRACARR

LBRTMCCL

Friday, July 06, 2018

LBRGSTRE

LBRJSMIT

LBREHART

LBRJMITC

Saturday, July 07, 2018

LBRBIROB

LBRMMANI

LBRMCOY

LBRSKENN

Sunday, July 08, 2018

LBRJOHNS

LBRGSTRE

LBRDHANN

LBRPMANS

Monday, July 09, 2018

LBRJSMIT

LBRRJONE

LBRLPING

LBRRGEIG

Tuesday, July 10, 2018

LBRMMANI

LBRLDILL

LBRCEMBE

LBRLBLAK

Wednesday, July 11, 2018

LBRGSTRE

LBRJHOWE

LBRLCLIF

Thursday, July 12, 2018

LBRRJONE

LBRNLAZO

Friday, July 13, 2018

LBRLDILL

<tbody>
</tbody>

I really wish I could upload this sheet for everyone to take a look at.

Thanks for any help I can get on this!

 

[Clooney]

Answered from another forum...works flawlessly!

This proposal makes a few changes to the arrangement of the Team's tables.
This proposal also adds a helper column (E) which may be moved and/or hidden for aesthetic purposes and is populated using:

=IF(WEEKDAY(A2,16)=1,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,5),4,5),IF(WEEKDAY(A2,16)=2,IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,4),5,4),IF(WEEKDAY(A2,16)<=4,IF(COUNTIFS(E$1:E1,4)< COUNTIFS(E$1:E1,1),4,1),IF(COUNTIFS(E$1:E1,5)< COUNTIFS(E$1:E1,1),5,1))))

The room 4 column is populated using:

=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,COUNTIFS(I$5:M$22,E2),MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))),IF(E2=1,1,IF(E2=4,3,5)))

The room 5&6 column is populated using:

=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,0,MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2)))+1,IF(E2=1,1,IF(E2=4,3,5)))
The room 7&8 column is populated using:

=INDEX(H$5:L$22,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=COUNTIFS(I$5:M$22,E2)-1,1,IF(MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2))=0,0,MOD(COUNTIFS(E$2:E2,E2)+2*(COUNTIFS(E$2:E2,E2)-1),COUNTIFS(I$5:M$22,E2)))+2),IF(E2=1,1,IF(E2=4,3,5)))