"Status" column to reflect 2nd column input for "Cancelled or On Hold"

Options

I have a Status column that is calculated via an IF statement referencing two other columns, [% Complete] and [Cancelled / On Hold].

=IF([% Complete]@row = "", "", IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started"))))

I would like the roll-up status of the parent to say "Cancelled / On Hold" if all Status fields are "Cancelled" or "On Hold" instead of "In Progress" as it does now.

Is that possible and what would I need to add to my formula to do so?

Thank you kindly.

Answers

  • Genevieve P.
    Options

    Hi @jbonetti


    Try the following:

    =IF([% Complete]@row = "", "", IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN([Cancelled / On Hold]@row), "Cancelled") + COUNTIF(CHILDREN([Cancelled / On Hold]@row), "On Hold") = COUNT(CHILDREN([Task Name]@row)), "Cancelled / On Hold"), IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))


    I'll break it down:

    =IF([% Complete]@row = "", "",

    This is the same as your current formula. Then we get into the question of hierarchy:

    IF(COUNT(CHILDREN([Task Name]@row)) > 0,

    If there are Children below this row (checking the Task Name to identify the number of children, since blank cells wouldn't be counted)

    Then, if the COUNT of "Cancelled" and "On Hold" is the SAME as the Count of ALL Children, return "Cancelled / On Hold"

    IF(COUNTIF(CHILDREN([Cancelled / On Hold]@row), "Cancelled") + COUNTIF(CHILDREN([Cancelled / On Hold]@row), "On Hold")

    =

    COUNT(CHILDREN([Task Name]@row)), "Cancelled / On Hold"),


    Finally, otherwise, the rest of your formula.

    IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))


    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!