Formula to Flag a Task if it's Predecessor is Delayed

Hi all,

I am trying to develop a formula that flags/checks a box for tasks whose predecessors are delayed. Please see below for additional requirements and my work/understanding so far:

Sheet Details:

  • I have Predecessor and Successor columns with those names.
  • My task column is named Milestone / Task
  • My finish date column is named target date. 
  • I have a % complete column called % complete.
  • I have a status column that auto populates based on the % complete column e.g., if % complete is 100% then status = 'completed'

Requirements:

  • If a task's predecessor(s) have target date(s) that finish after the start date for the task and % complete for the predecessor is not 100%, then flag the task row (checkbox or flag column type). 

Work completed so far:

  1. Created a text/number auto number column called 'Auto'
  2. Created a text/number row number column called 'Row' - =MATCH(Auto@row, Auto:Auto, 0)
  3. Created a multiselect dropdown column called 'PredHelp' to convert Predecessors into characters =SUBSTITUTE(Successors@row, ", ", CHAR(10))
  4. I have attempted a formula but am not sure how to get it to look at the target date in the predecessor row and not the target date in the task row:    

=IF(AND(PredHelp@row <> "", [Target Date]@row > [Start Date]@row, [% complete]@row <> 1), 1, 0)

Thank you and any help would be appreciated!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My suggestion would be to also include some type of flag on a row by row basis to flag each task that is behind. Then you can use a COUNTIFS to count how many rows are flagged and are a predecessor. Dropping that COUNTIFS into an IF statement to say "if the countifs is greater than zero, flag" will give you a flag if any predecessor is also flagged.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com