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 03, 2021

Cambosity100 

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)
Cambosity100's avatar
Cambosity100
Brass Contributor
Aug 04, 2021

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 🙂

Resources