Getting Status of Children to roll up to Parent Row
I have Parent tasks with Children tasks in a dropdown list column. The options for the children are: assigned, in progress, complete.
would like to have the parent status display be blank if none of the children have a status selected (all are blank), display "Complete" only if all the children have a complete status, display "Not Started" if all of the children have an assigned status, and display "In Progress" if any of the children have an In Progress status (or as the default display). I've been playing with formulas for two days and can't get it to do that. I think the problem is it's not counting blank cells. But I need it to count blank cells for this to work the way I have it set up.
Below is my current formula. Can someone please help me.
=IF(COUNTIF(CHILDREN([Task Name]1), " ") = COUNT(CHILDREN([Task Name]1)), " ", IF(COUNTIF(CHILDREN([Task Name]1), "Complete") = COUNT(CHILDREN([Task Name]1)), "Complete", IF(COUNTIF(CHILDREN([Task Name]1), "In Progress") >= 1, "In Progress", IF(COUNTIF(CHILDREN([Task Name]1), "Assigned") = COUNT(CHILDREN([Task Name]1)), "Not Started"))))
Comments
-
Remove the spaces between your quotes to account for blank cells.
Instead of " ", try "".
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!
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