Update Status of Dependent task to Go Ahead if all Predecessor Tasks are in Done Status

Options
Venk
Venk
edited 07/01/24 in Formulas and Functions

Hi,

Using Automation and helper columns, is it possible to update the Status of a Dependent task to Go Ahead if all of its Predecessor Tasks are in Done Status?

Thanks,

Venkat

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 07/05/24
    Options

    Yes, it's a little complicated but yes.

    1. Setup an AutoNumber column called Auto. (or if you have one already, that works too).
    2. Setup a Row Number column with the following column formula: =MATCH(Auto@row,Auto:Auto,0)
    3. Setup a Successors column with the following column formula: =JOIN(SUCCESSORS(Predecessors@row),",")
    4. Setup a Go Ahead column as a checkbox column with the following column formula: =IF(COUNT(COLLECT(Status:Status,Status:Status,"Done",Successors:Successors,CONTAINS([Row Number]@row,@cell))) = COUNT(COLLECT(Status:Status,Successors:Successors,CONTAINS([Row Number]@row,@cell))), true)
    5. When the Go Ahead checkbox becomes checked, you can run an automation to set your Status to "Go Ahead" for that task.

    The way this works is:

    • The first COUNT/COLLECT formula counts up a collected list of all the row Statuses that are marked "Done" that have the current task as one of their Successors.
    • The second COUNT/COLLECT counts up a collected list of all the row Statuses of any value, when it has the current task as one of the Successors.
    • The formula then compares the two counts. If they are equal, then you know that all Predecessors are marked as Done
    • The automation then marks your Status for you. You cannot use a formula in the Status column that refers to the Go Ahead column, or you'll get a formula loop.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!