Parent/Child Formula Help
Hi - hoping to get help crafting a formula. I'm using Smartsheet for systems testing and need an easy way to identify when sets of scenarios are complete or still in progress. The rows in the sheet are in hierarchies with the parent row representing the overall scenario description and the child rows representing the individual steps that need to be executed. Each row has a status column. In another column, I need a formula that looks at the status column and returns the value of Complete or In Progress for the entire scenario taking into consideration the parent + child statuses. Here's the list of statuses whether it would be considered In Progress or Complete:
Unexecuted = In progress
In Progress = In Progress
Retest = In Progress
Fail = In Progress
Pass = Complete
Fail - passed on other scenario = Complete
Out of Scope = Complete
Deferred = Complete
So basically, what I'd like in this other column (I think) is something along the lines of: if the parent or child includes any of the In Progress statuses, return In Progress, otherwise return Complete.
Thank you so much in advance - I'm very intermediate creating formulas like and might be able to figure this out but it would literally take me an hour or two of trial and error!
Answers
-
Fir the parent rows try:
=IF(Count(children()) = countif(Cildren(),="Complete"),"Complete, "In Progress")
The formula counts the children and then the children that are complete. If both are equal the parent is complete. For this to work all of the children have to have data in the row.
Partial answer?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!