Automate Parent Status Rollup based on Descendants' statuses

Options

Hi folks — has anyone figured out how to enable users to update statuses manually for child rows, and then have the parent rows’ status update automatically? There are multiple levels of hierarchy.

For example, I have three statuses that a child row can be; In Progress, Not Started, Completed. 

I have figured out the correct combo of “If”, “CountIfs”, “Descendant” functions etc. It works great if I use Row formulas and the user only changes the child rows.The logic is:

  1.  if all the descendants are “Not Started” then the parent is “Not Started” or
  2. if all the descendants are “Complete”, then the parent is “Complete” or
  3. if neither above is true, then the parent is “In Progress”.

However, I have these problems that prevent this from actually working in the real world:

  1. Using row formulas in the status column means that if a user manually changes the status in the row, the formula is deleted and replaced with the status. This is ok if the row remains a child and doesn’t become a parent. If the row becomes a parent at some time in the future, it won’t have the formula and won’t be auto update. 
  2. Using Column formulas in the Status column prevents a user from manually changing the status on a row.  

I tried using Workflows, but it seems like it’s not possible for a workflow to evaluate the descendants the way I want it to. 

Thanks,

Marc

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!