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!
Help Article Resources
Check out the Formula Handbook template!