I have seen in the past quite a few people asking how we can leverage predecessors for alerts so that when (for example) the preceding task is complete, a notification goes out automatically to the owner of the next task letting them know it is ready to start. The best approach to this was unfortunately parsing out the predecessors across multiple columns (if you had multiple predecessors).
I have been playing with the new SUCCESSORS function, and think I have almost found a way (with certain limitations). The limitations with this would be if you specified "SS", "SF", "FS", or "FF". I think I have some ideas on how to accommodate that but want to do some more testing before putting it out here for everyone.
In the mean time... Please feel free to take a look at the below to get started . I also welcome feedback and ideas on how to adjust for the other variables such as lag/lead time, "SS"/"SF"/etc., and any other "catch" people can think of.
First we insert an Auto-number column with no special formatting (called "Auto").
Next we insert a text/number column (called "Row") and use the following column formula:
=MATCH([email protected], Auto:Auto, 0)
Then we insert a multi-select dropdown column (called "Next Tasks") and used this column formula:
=JOIN(SUCCESSORS([Task Name]@row), CHAR(10))
From here we you can start to deviate and adjust as needed, but the below formula (put into another text/number column) will grab the % Complete for the previous tasks, add them up, then compare it to how many previous tasks there are. If those two numbers match, then the task on this row is ready to start.
=IF(AND([email protected] <> "", [% Complete]@row = 0, SUMIFS([% Complete]:[% Complete], [Next Tasks]:[Next Tasks], HAS(@cell, [email protected] + "")) = COUNTIFS([Next Tasks]:[Next Tasks], HAS(@cell, [email protected] + ""))), 1)
In the below screenshot, you will see that Row 5 is ready to start because its predecessor (row 4) is at 100% complete, and row 5 has not yet been started. Row 9 is not yet ready because it needs both rows 7 & 8 to be completed first and 8 is only at 50%. You will also see that Row 8 is not marked as ready to start because even though its predecessor is completed, the task on Row 8 has already ben started (% Complete greater than zero).