Forum Discussion
Simpler Roster Conflict
Hi all. I have had help with something similar to this in December last year. This is simpler. I think. Just need a formulae for conditional formatting so that we don't roster people to conflicting shifts for each day. The drop down lists are linked to each clients individual teams in the teams sheet. If a shift conflicted it would be great if both shifts turned red. This allows us to either select a different staff member or adjust the times for the shift. This sample is just a mini version of the task we have. Normally it is 40 + staff and 60 + clients. Also if anybody has an idea for this. To the right of the spread sheet is a list for accumulating hrs so that we can track each staff members hrs as we go. I have done this before with LOADS of embedded IF 's. There must be a better way 🙂 With thanks in advance to this wonderful community...
Hopefully this is better; I rather missed the point on the conditional formatting. This time I defined a Boolean 'Conflict?',
= IF(ISTEXT(currentAssigned), COUNTIFS( otherAssigned, currentAssigned, otherStart, "<"¤tFinish, otherFinish, ">"¤tStart ) > 1)based upon relative references, to control the format.
11 Replies
Hopefully this is better; I rather missed the point on the conditional formatting. This time I defined a Boolean 'Conflict?',
= IF(ISTEXT(currentAssigned), COUNTIFS( otherAssigned, currentAssigned, otherStart, "<"¤tFinish, otherFinish, ">"¤tStart ) > 1)based upon relative references, to control the format.
PeterBartholomew1 Hey Peter I have attached an expanded version of the sample roster. All of my attempts to expand the calculations seem to give me #value! Sorry mate your formulae is currently out of my depth. I'm not sure what I am doing wrong ? 🙂
Since the formulas all use defined names, it is important to confirm that the ranges you have identified by name are correctly sized and do actually contain the data you wish to reference. A list of names can be generated by selecting Formulas/Defined Names/Use in formula/Paste Names.../Paste List.
To redefine the Names one uses Name Manager to reselect the correct/new ranges. Sometimes it is convenient to extend a range by inserting additional rows in the middle, since it is the top and bottom corners that define a multi-cell range. It can be a good idea to use Tables where the data is likely to extend on a regular basis, because Structured References and any Names that reference them will be fully dynamic and adjust to the data.
I have reattached the previous copy of the file because the data validation dropdowns seems to have got corrupted in the new version.
PeterBartholomew1 Peter that's absolutely perfect ! Just some questions about importing it into the real world. I can't even find where the formulas are LOL. With Hours Tallied am I able to drag the Formula? As there are about 40 staff and growing. And do I just extend the range of the Conditional Formatting to accommodate 60 +Clients and growing ? Also could you find a way to display (maybe on a new sheet ?) the quietest an busiest times of each day, say hour by hour so that we can plan times for staff meetings ? I think I can do it but I would love to see your solution ! I hope I am not asking too much 🙂
I was rather hoping someone would have posted a solution to this question before I posted 'work-in-progress' which hopefully will lead to a completely dynamic solution once I have the new Lambda helper functions. As it is, I still have some relative referencing.
The underlying data is essentially 3 arrays (rostered team, start time and finish time) combined by interlacing rows and columns. Although it is easy to extract the constituent arrays, aggregations (normally requiring COUNTIFS) are tricky when one is working with arrays rather than range references. Even something as simply as summing the rows is not easy when working with arrays (MMULT could be used) but, as a temporary fix, I have fallen back on relative referencing.
The conditional formatting seems to work. Being antiquated functionality, there is no alternative to single-cell, relative referencing.
Conditional format
= IF( ISTEXT(currentCell), COUNTIFS(currentColumn,currentCell) > 1)PeterBartholomew1 Also Peter, the hours tallied weekly formula is brilliant. A million times better that embedded IF's 🙂 Now I need to research LET .... Thanks again 🙂
PeterBartholomew1 Hi Peter. Thank you I really appreciate your interest in this problem. I have looked at your Roster for further work and in its current state it appears to not be picking up time conflicts but name duplicates. For example on Thursday Staff 3 could easily service Client 1 and Client 8 as there is a 5 hr gap between the shifts. Also, for example, options for conflict resolution on Tuesday I would probably either select another Staff or get permission to "slide" the Client 2 shift forward one hour making it 1000 to 1600 hence no longer conflicting with Client 4. Client 11 would need another Staff member. With this in place the red should disappear. I hope this makes sense. So in short in this industry it is common if not the norm for a Staff member to do multiple shifts per day. Its just making sure the times don't conflict. 🙂 Once again my thanks for your help...
