Child task status rollup to Parent task
Hi there,
I am trying to do a formula that says something along the lines of the below:
- If all tasks are marked as complete, update parent to Complete
- If any task is marked as 'in progress' update parent to In Progress
- If no tasks are started, update parent to Not Started
- If majority of tasks are marked as Potential Risk OR Risk / Delay update parent to At Risk
- If all tasks are NA / canceled, update parent to Canceled
My status options are:
| ||
| Not Started |
|
---|---|---|
| ||
| In Progress |
|
| ||
| Potential Risk |
|
| ||
| Risk / Delay |
|
| ||
| Complete |
|
| ||
| NA /Canceled |
|
I tried looking at other community posts, and found this, but I'm not sure how to incorporate my additional statuses.
=IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") > 0, "Not Started", "Complete"))
Please can someone advise? Appreciate any help!
Thanks!
Rosie
Answers
-
Hi RosieO,
To incorporate your additional statuses and meet your specific conditions, you can try below formula:
=IF(COUNTIFS(CHILDREN(), "NA /Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Potential Risk") + COUNTIFS(CHILDREN(), "Risk / Delay") > COUNT(CHILDREN()) / 2, "At Risk", "")))))
I hope this helps!Thanks!
Leela Lodhi -
Hi Leela, thanks so much for this!
I have one tiny thing thats not working, which is that if the child task is blank, the formula displays as 'Canceled' and I am not sure how to fix to say if the status is blank, display 'Not Started' OR display nothing. Just don't think that Canceled is accurate for this. See screengrab below.
Thank you so much for any additional guidance!!!
-
@RosieO i suggest you to add one or two helper coloum to get a better overview where you are . =COUNT(ANCESTORS([Activity / Item]@row)) same for children.
Try this updated formula considering the blank. =IF(COUNTIFS(CHILDREN(), "NA /Canceled") = COUNT(CHILDREN()), "Canceled",
IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete",
IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress",
IF(COUNTIFS(CHILDREN(), "Not Started") + COUNTIFS(CHILDREN(), "") = COUNT(CHILDREN()), "Not Started",
IF(COUNTIFS(CHILDREN(), "Potential Risk") + COUNTIFS(CHILDREN(), "Risk / Delay") > COUNT(CHILDREN()) / 2, "At Risk", "")))))If my comment helps you, I appreciate a 💡
Kind regards
Event: Strategies for Successful Adaption
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
https://www.eventbrite.de/e/driving-change-with-smartsheet-strategies-for-successful-adoption-tickets-1047813557557?utm-campaign=social&utm-content=attendeeshare&utm-medium=discovery&utm-term=listing&utm-source=cp&aff=ebdsshcopyurlWe offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
-
Thanks Nico for coming back so quickly. I already have about a million columns in my sheet so was trying to avoid adding anything else if I can.
Apologies for this, but I'm not sure what the difference is between the original formula Leela provided and the one you have provided above?
Thanks!
-
@RosieO my formula accounts for both “Not Started” and blank child tasks by using the following condition:. i hope its alwasy bit more difficult to draft a formula if you not working in the actual sheet.
If my comment helps you, I appreciate a 💡
Kind regards
Event: Strategies for Successful Adaption
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
https://www.eventbrite.de/e/driving-change-with-smartsheet-strategies-for-successful-adoption-tickets-1047813557557?utm-campaign=social&utm-content=attendeeshare&utm-medium=discovery&utm-term=listing&utm-source=cp&aff=ebdsshcopyurlWe offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
-
Hm - I appreciate the help, but I am getting the same outcome in Smartsheet:
I published a test sheet here:
if thats helpful?Thanks in advance!
-
If my comment helps you, I appreciate a 💡
Kind regards
Event: Strategies for Successful Adaption
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
https://www.eventbrite.de/e/driving-change-with-smartsheet-strategies-for-successful-adoption-tickets-1047813557557?utm-campaign=social&utm-content=attendeeshare&utm-medium=discovery&utm-term=listing&utm-source=cp&aff=ebdsshcopyurlWe offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!