Forum Discussion

Cambosity100's avatar
Cambosity100
Brass Contributor
Aug 03, 2021
Solved

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 s...
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Aug 04, 2021

    Cambosity100 

    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,  "<"&currentFinish,
       otherFinish, ">"&currentStart
       ) > 1)

    based upon relative references, to control the format.

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 04, 2021

Cambosity100 

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,  "<"&currentFinish,
       otherFinish, ">"&currentStart
       ) > 1)

based upon relative references, to control the format.

 

  • Cambosity100's avatar
    Cambosity100
    Brass Contributor
    Aug 04, 2021

    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 ?  🙂

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Aug 04, 2021

      Cambosity100 

      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.

      • Cambosity100's avatar
        Cambosity100
        Brass Contributor
        Aug 11, 2021

        PeterBartholomew1 

             Hi Peter. Sorry for the delay in contacting you. I have just been through a massive house move with my family.

             I have tried to expand the ranges to the formulas without success. Is there a name for this style of formulae. Usually when I have had help from the community it will create an Ah Ha moment. With this it is totally foreign. Another big area to study in Excel LOL. 

             I have fixed the drop down validation boxes though. As I have never seen this side of excel before I feel pretty out of my depth. I am assuming that once this is working I can substitute "Staff " and "Client" with real names ? Are you still able to assist me ? I am very grateful for your time...

         

             Kind regards Peter

  • Cambosity100's avatar
    Cambosity100
    Brass Contributor
    Aug 04, 2021

    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 🙂