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:
- Created a text/number auto number column called 'Auto'
- Created a text/number row number column called 'Row' - =MATCH(Auto@row, Auto:Auto, 0)
- Created a multiselect dropdown column called 'PredHelp' to convert Predecessors into characters =SUBSTITUTE(Successors@row, ", ", CHAR(10))
- 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
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives