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

Mellow

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!

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.