Employee rotation, taking into account expected absences.

[EWSDustin]

Good morning!
Here again looking for wisdom.

What I would like to learn is that when I add an employee in column (C) "Absences", that it removes the name from the rotation for that week, and automatically recalculates the order for the next weeks. I will attach a very simple Excel with all possible cases to give context.
Excel example,
- In week 3, "C" and "D" will not be working in the Panda Hugging department (the day was long, I don't have much imagination left). So the moment I entered their names in "Absences", they automatically left week 3, and the rotation for the rest of the weeks will be updated.
- In weeks 4 and 5, there are no absences in the Caring for deserted islands department. Therefore, nothing changes.
- In week 6, Mr. D is absent, but he was not scheduled to be in the Hotel Mattress Inspector department that week either ( well thought, he is lucky). Therefore, nothing changes.
- In week 7, we have "E" absent from the prestigious department of Beer Taster... without alcohol. And just as with week 3, his name will leave that week and will be updated in order over the next few weeks.

Of the archaic methods I have tried, nothing ends up working. So I would appreciate if you can help me again with this Excel.
Thank you very much in advance,

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Employee rotation, taking into account expected absences.
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[EWSDustin]

Hi to all,
I changed the Excel to avoid this problem, so its no longer needed, thanks in advance!!