Forum Discussion
Roster Conflict
OK just looked further into your amendments. Restrictions on the data validation go to far. For example if I needed to give Staff H a 2nd or 3rd shift they would not appear in the drop down box.Also I J and K as casuals may not be available for shifts this week... Mind you if it displayed potentially available staff, shift by shift, in the drop down box it would be incredibly powerful. I am daily asked to find ways to cover shifts due to staff illness / unavailability or pop up shifts from unexpected appointments with participants... And yes it would be excellent if the highlighting occurred on both examples of the conflict. Thank you so much your formulae will actually have a real positive impact on lives in our society...
Cambosity I agree with mathetes comments about this being a human readable sheet and such, but I think I get what and why your are doing it. That said in the attached I did 2 things:
I updated the sheet where you list the options for the data validation (drop down list) so that for each shift you can only select an employee that hasn't been selected yet for that shift but was selected for a previous shift (i.e. "A" can't have a shift 1 and then a shift 3 without being assigned a shift 2 and "A" can't be assigned 2 shift 1's)
I created the conditional formatting that looks if the start of this shift begins before the previous shift ended. I only highlighted the "new" shift that created the conflict but could easily highlight the previous shift also if needed.
I hope that helps you and your team keep doing good things for these people.
OK just looked further into your amendments. Restrictions on the data validation go to far. For example if I needed to give Staff H a 2nd or 3rd shift they would not appear in the drop down box.Also I J and K as casuals may not be available for shifts this week... Mind you if it displayed potentially available staff, shift by shift, in the drop down box it would be incredibly powerful. I am daily asked to find ways to cover shifts due to staff illness / unavailability or pop up shifts from unexpected appointments with participants... And yes it would be excellent if the highlighting occurred on both examples of the conflict. Thank you so much your formulae will actually have a real positive impact on lives in our society...
Cambosity Ooops. It didn't go "too far" it was just wrong. lol. I thought I had a clever shortcut using the previous list but that was just wrong. The new dynamic link is a more complicated formula but I believe it is correct now.
Also here is a copy of the actual roster just cleaned of recognizable data. I could not work out how to insert your formula LOL way to advanced for me !
Cambosity The drop down seem to be working correctly for me. They do NOT stop conflicts since that would depend on the time that you are going to enter. Re-looking at this chart I might see my misunderstanding but not sure what to do about it. So the drop down check 2 conditions:
1) the same staff provider can NOT be selected 2x for the SAME shift
2) a staff provider must have a previous shift to be selected for the shift. i.e. staff member "A" must have a Shift 1 before they can be selected to have a Shift 2
I think my problem here is that those "shifts" are CLIENT shifts NOT staff member shifts. Therefore, I suppose a staff may be added to the "3rd shift" for a client even though it is THEIR second shift. Here is a new conditional formatting formula that I think will work for this general case (based on the original sample sheet):
=SUMPRODUCT((C3>" ")*(C3=$C$3:$I$21)*(C4>$C$4:$I$22)*(C4<$D$4:$J$22))+SUMPRODUCT((C3>" ")*(C3=$C$3:$I$21)*(D4>$C$4:$I$22)*(D4<$D$4:$J$22))I will try to apply this to this updated sheet you posted, but there is a LOT going on here and I'm not sure I understand it all. That said you have some CRAZY nested IF statements. I bet you could make them much more simple based on an array but at the VERY LEAST you can use IFS() function. So I adjusted and applied that formula to RWk43 Monday columns. See attached. BTW, I dropped that custom data formatting/drop down lists because that would be a huge mess with so many days/weeks/sheets/etc... and probably doesn't help with what you need it to do anyhow (based on my comments above)
I think something went awry with my last message... Drop down lists are still not quite right. They are giving staff that will still be in conflict. Maybe if we have more staff in the sample ? I have increased the staff to the letter t making it B21 and adjusted the formula accordingly but it is still giving me the wrong staff. Any chance another look... ? 🙂 The conflict conditional formatting is brilliant !!
With @mtarler I commend you, Cambosity , on the good work you are doing for people very much in need. My youngest brother (who passed away last year at the age of 70) was one in that NDIS client community, so I know firsthand the importance of the work.
I also want to compliment @mtarler on the good Excel work he's done here to help you. He was able to see through the stuff that just raised questions in my mind. An example of how great this techcommunity is.
