Hi All,
This is a little long winded but please bear with me.
I have a roster I have developed, but for simplicity reasons, and the need to take away Human Error I am trying to have this roster show when there is a coverage issue across 1 of the 3 departments (Prod, D&B, Survey). I currently have countifs which determine if there is less than 3 personnel on site or if there is no one able to provide coverage for one or more of the departments on any given day. See the spreadsheet below;
Roster Problem.xlsx
While this works well for days 7 and 8, for example, Day 12 should also return a "CHK" Message. Whilst there is 3 personnel on site and there is someone that can cover each of the departments, it is in fact the same person (Tim) who is able to perform both Prod and Survey Duties. However, 1 person can only perform one duty and thus he should be discounted from survey as he will be required to perform the prod role.
So my question is, how can I adjust this formula to take into account situations such as Day 12, where there will be no coverage for either the prod or survey roles?
Thanks for your help in advance,
Cheers,
Spicey
Bookmarks