Update Parent Row Status based on Status of Child, GrandChild rows
I've got a lot of helper columns already, in trying to track an enterprise level project.
I've got a status column.
I've got a progress column that is weighted based on the status columns.
I'd like to automatically update the ancestor and parent rows based on the status of the child rows below it.
So, if someone moves a task to In Progress, the ancestor rows should update to in progress.
I've tried writing a formula for if the progress is less than 100% but greater than zero.
I've tried writing every formula and googled everything I can think of. It's just not a simple formula and I'm ready to give up and just tell everyone their going to have to manually update every parent row if they make a change. This will negatively affect our adoption of Smartsheet.
Any suggestions would be appreciated.
Best Answer
-
Are you able to provide a screenshot for reference?
I am thinking a nested if should work.
=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), @cell = "In Progress")>= 1, "In Progress", "Not Started"))
Answers
-
Additionally if 100% of the child rows are in "Completed" status or the progress of 100% of the children rows are at 100%, I want the parent row's status to update to "Completed".
I've added additional helper columns to see if I could write automations or workflows based on that column, but I can't get the formulas correct.
-
Are you able to provide a screenshot for reference?
I am thinking a nested if should work.
=IF(COUNTIFS(CHILDREN(), @cell = "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), @cell = "In Progress")>= 1, "In Progress", "Not Started"))
-
@Paul Newcome thanks for the suggestion. It sort of worked, but, when I put the formula into the status column, I lost the ability to update the status of the children tickets. I think I'm just going to have to stay on top of everyone to update the parent rows.
Thanks!
Jess
-
Yes. Applying a column formula will remove the ability to manually update the child rows, but you have 3 options...
Manually put the formula in each parent row.
Develop a formula that can also automatically calculate the child rows and nest it into the column formula.
Use a helper column to house the formula and manually fill in the child rows in the existing column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!