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...
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)
Amazing ! The conflict thing works perfectly I have applied it successfully to my active sheet and it is just brilliant.... Also, after thinking it through (I reckon I got over excited with possibilities), I think it best as it is with all staff selectable for each shift as it allows the rosterer choice. A shift conflict can be resolved in 2 ways. 1/ By changing the staff member or 2/ slipping the shift, so a 3hr shift from 9 til 12 might become 11 till 2. This is not always possible but often is depending on the flexibility of the NDIS plan.
I had a crack at applying it to Tuesday but must have done something wrong came out all buggy finding conflicts where there were none.
Have noticed 1 Monday bug. E14 does not highlight. Its counterpart conflict will.
Also thanks for the tip re IFS and arrays....yep there were some crazy hours put into my nested IF hehe... wish I'd known about them when I started LOL !!!
If you look to the right of the screen you see staff hours totals. Would it be possible to have those accruing totals in the drop down list next to the staff. Once again it would save scrolling backwards and forwards across the screen and aids in staff selection as some staff are permanent part time anywhere between 10 and 30 hrs and others are casual ?
Once again I must let you know of my gratefulness. Thank you I am learning heaps in the process as well... 🙂 Cambo
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 !!