Parent/child formula working/not working depending on position in hierarchy

Suzanne Raphael
Suzanne Raphael ✭✭✭
edited 02/10/20 in Formulas and Functions


I have 3 drop-down menu columns to indicate pass/fail/untested, in a QA testing sheet. Via rows with parent-child hierarchy, the sheet is further divided up into test months, weeks, cases, and sub-cases.

Toward the goal of ultimately creating a formula that says, "as a parent row, all of my children must pass before I can pass; if any of my children fail, I fail; else I am blank," I started with using the same formula for each parent row to stay blank until/unless all children pass:

=IF(AND(COUNT(CHILDREN([<Column header name>]@row)) = COUNTIF(CHILDREN(), "P")), "P", "")

Bizarrely, this works fine everywhere, EXCEPT the "uber" parent, which in this case is the Month row. Despite the fact that none of its children nor its "grand" or "great grand" children have passed, this row passes itself. As seen below, the blank rows each have the formula applied to them, and they are behaving as expected (I don't have everything expanded to keep my grab compact):

What. In. The. World? Am I asking too much of Smartsheet??

Thanks in advance for any tips!!



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!