Parent status based on Child status
Hi-
I am currently working on something for a group that does not want to automate their child task statuses. I have scoured the answers here to try and find anything that I can customize to work with their parent rows for an automated update, but I keep coming up a little short. I'm not sure if it's maybe too many combinations, or if I'm just not writing it out correctly so any help would be appreciated!
They have the following statuses on their sheet-
· In Progress
· On Deck
· On Hold
· Canceled
· Complete
What I'm trying to show is -
· If any children tasks are In Progress the parent=In Progress
· If all children tasks are On Deck the parent=On Deck
· If all children tasks are On Hold the parent = On Hold
· If all children tasks are Canceled the parent=Canceled
· If all children tasks are Complete the parent=Complete
· If there is a combination of On Deck, On Hold, Canceled, and Complete parent=In Progress
· If there is a combination of On Hold and Canceled OR On Hold & Complete, parent=On Hold
· If there is a combination of Canceled and Complete parent=Complete
· If the children rows are blank parent=blank or On Deck if blank is not possible
The other part I’m having an issue with is they wanted an auto-flag for At Risk, I’ve created that, but I’m not sure how to tie it into the status updates to show the parent as At Risk if the flag is checked.
Best Answer
-
Give this a try...
=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "On Deck") = COUNT(CHILDREN()), "On Deck", IF(COUNTIFS(CHILDREN(), @cell = "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Canceled", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold")> 0, "On Hold", "In Progress"))))))
Answers
-
Give this a try...
=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "On Deck") = COUNT(CHILDREN()), "On Deck", IF(COUNTIFS(CHILDREN(), @cell = "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Canceled", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold")> 0, "On Hold", "In Progress"))))))
-
Almost, yes, thank you so much!
It's that On Hold part that is making it not work quite as it should. It should only reflect as On Hold if all of the children are On Hold. Is there any way to get it to show In Progress if there is a combination of statuses like this:
EDIT:
Actually. I just answered my own question by changing the ending of the statement. Thank you again for the support, I appreciate it!
=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "On Deck") = COUNT(CHILDREN()), "On Deck", IF(COUNTIFS(CHILDREN(), @cell = "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Canceled", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", "In Progress"))))))
-
Glad you got it sorted. 👍️
-
@Paul Newcome - I am hoping you can help me with something similar: I need this formula to return Complete if the other Child statuses are some thing else:
=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "Getting Requirements") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Requirements Complete") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Development In Process") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Ready for Review") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Feedback sent to MHI") = COUNT(CHILDREN()), "In Progress"), "Complete"), "Complete"), "Complete"), "Complete"), "Complete")
I know this is incorrect, I am just not sure where to put the "Complete"
-
@liz_alvarez Are you able to explain everything you are wanting to accomplish with this formula?
-
@Paul Newcome - yes!
I have these statuses:
- Getting Requirements
- Requirements Complete
- Development In Process
- Ready for Review
- Feedback sent to MHI
- Complete/Signed Off
- No Longer Needed
For all of these - Getting Requirements, Requirements Complete, Development In Process, Ready for Review, Feedback sent to MHI - I want the parent task to be "In Progress"
For "Complete/Signed Off", I want the parent task to be "Complete"
No Longer Needed - can be excluded
Thanks for taking a look!
Liz
-
@liz_alvarez Try this:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete/Signed Off", @cell = "No Longer Needed")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", "In Progress")
-
@Paul Nelson - That is going to work; how would it need to be written so that if the status of the children are all blank, that the parent is blank?
Thanks so much for all of the help, I really appreciate it!
Liz
-
@liz_alvarez It would look like this:
=IF(COUNTIFS(CHILDREN(), @cell <> "") <> 0, IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete/Signed Off", @cell = "No Longer Needed")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", "In Progress")
-
That is exactly right! How odd!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives