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'


  • 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!