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

Suzanne Raphael

Ugh!

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!!

Cheers,

Suzanne

Paul Newcome

Because when you use something such as

COUNT(CHILDREN()) (even with a cell reference in there)

it counts non-blank rows. So depending on which column header you are referencing, if there are no rows with data in them, then the count would be 0 which matches the 0 count of "P" cells in the immediate column.

Frank Falco

Try using the DESCENDANTS([parent_cell]) instead of the CHILDREN command

Suzanne Raphael

Thank you, @Frank Falco, that surely did the trick for the first part.

But now I'm struggling to implement the second part, "If any of my children are "F", I'm "F"."

I have yet to be struck with the eureka moment for beyond basic/multiple variable formulas. I'm really operating out of mimicry. I've done a lot of searching to find a video tutorial that walks through "if this(1), then that(1), or if this(2) then that(2), else that(3)), and doing such comparing cells v. rows v. columns. Any pointers to make me smarter, i.e. actually watching someone build out such a formula would be super helpful. I have a wizard friend who says "start from the middle," but I'm missing some basic understanding about "ifs" "ands" and "ors"...just not sinking in.

Andrée Starå

Hi, @siouxzie7

This is an excellent resource for playing around/understanding the formulas.

Smartsheet Formula Examples

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Suzanne Raphael

Thank you, @Andrée Starå , but I don't really find it to be very valuable at the moment. For one thing, a lot of the examples in that doc are focused on calculating numerical values, and none that I find really deal with this, that or the other thing with the behaviors of each being varied.

If I'm honest, I can't even break down what's happening here (original, semi-functional formula from above):

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

Like for that, why wouldn't =IF(COUNTIF(CHILDREN(), "P"), "P", "") work? What is the function of the "AND"? Why must I "count" then "countif"? I feel like if I could take a handful of the formulas I'm using and reverse-engineer them, understand what functions are doing what, then I'd be able to move forward with understanding. But I have just not been successful so far with this effort.

If you look at my original post on this thread, I have multiple things going on here—passing all children passes the uber parent. Failing any fails the uber parent. The uber parent should be blank if it hasn't passed or failed. Right now I'd really love some help making all this happen, just to get through the day, but I really want to learn and stop having to post here :-/

Thanks, in frustration :-(

Suzanne

Paul Newcome

Because when you use something such as

COUNT(CHILDREN()) (even with a cell reference in there)

it counts non-blank rows. So depending on which column header you are referencing, if there are no rows with data in them, then the count would be 0 which matches the 0 count of "P" cells in the immediate column.

Suzanne Raphael

"It counts non-blank rows" OMG, that answers SO MUCH. Thank you, @Paul Newcome.

Paul Newcome

@siouxzie7 Happy to help! 👍️

Andrée Starå

@siouxzie7

Happy to help!

Center of Excellent is also an excellent resource together with the functions list.

More info:

https://help.smartsheet.com/learning-track/getting-started-training/demand-training

https://help.smartsheet.com/functions