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"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!