Status Rollup to Parent

Options

I've seen some very similar solutions to my problem, but not quite what I am looking for.

My Smartsheet plan has a status column and is using dependencies. My choices in the status column are (Not Started, In Progress, At Risk, and Complete). I would like to create a formula that automatically rolls up the parent of each child status. If one child task is "IN Progress" then the parent task is "In Progress. If no child task, has started, then the Parent Task is "Not Started". If one child task is "At Risk" then the Parent task is at Risk. At Risk is the predominant concern. If we have a mix of "At Risk and "in Progress" then, At Risk should be displayed.

I appreciate any help you can offer.

Answers

  • Kiela
    Kiela ✭✭✭✭
    Options

    Kia ora,

    I have an admin column where I allocated the parent as 1 and my status is changed based on my % complete, i.e 0 = not started. Once a number is put into the % complete it then turns it to "in progress", at 100% it then changes to complete. I also have a "on hold" column with a flag so if that is ticked then the row goes red and is flagged. The formula I use to change the parent status is below.


    =IF(Admin@row = 1, "", IF([On Hold]@row = 1, "On Hold", IF([% Complete]@row = 1, "Completed", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In progress", "Not started"))))


    Hope that helps

    KL

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!