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 12, 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

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

        Cambosity100 

         

        Excel always had more capability than the majority of users knew about.  For example defined Names and CSE Array formulas were present in Office 1992.  Since Excel references have no way of drawing upon the business significance of the data, the default is to reference data by its location on the sheet using the A1 notation.  The Microsoft documentation does give lukewarm support for the use of defined names but most users stuck with the default.  Array formulas are typically used as methods of last resort, the assumption being that end users wouldn't know what they were.

         

        In 2007 Tables were introduced which provided a structured approach to referencing data which adjusts to match the content.  I would recommend their use for all source data.

         

        We are now in the middle of a period of seismic change which, for me, renders traditional spreadsheet solutions obsolete, though many will persevere with their use, unaware of the change that has occurred.  Towards the end of 2018 dynamic arrays were announced, which made array formulas the default and required additional work to identify a singe element from an array (no more CSE).  

         

        The LET function has now been introduced in all versions of 365 and allow a nested formula to be broken down into steps by applying local names to the inner parts of a formula, so that it can be addressed by name when used subsequently, one or more times.

         

        Still in beta testing are the Lambda functions.  This allows a function to be build by writing a formula using dummy variables and only then passing the actual parameters to the formula.  This allows the developer to use the same function in different contexts within a workbook.

         

        The latest development is a number of helper function that act on a user-defined Lambda function to feed it an array of parameters, one at a time, and then gather the set of results back as an array.

         

        So, if I were to write formulas for my computer, they would look very different again.

         

Resources