Checkbox condition in a child row, conditional formatting of parent?

Suzanne Raphael · 07/15/19

I have an uber parent row that is the mother of a group of related tests for software QA. In addition, some of the children of that mother have their own children.

I would like any parent row to turn red if any of its children fail a test, as noted by a "Fail" checkbox column, and bonus points for conversely turning green when all of the children have passed (as denoted by a "Pass" checkbox column).

Possible?

Cuatro · 07/15/19

I am looking for a similar solution, in my post too. Thanks for posting this.

Cuatro

mkingsbury · 07/15/19

Using a formula in your parent row in that column would make it auto check when at least one child row is checked.

=IF(COUNTIF(CHILDREN(), 1) > 0, 1, 0)

https://app.smartsheet.com/b/publish?EQBCT=31c3d12b4e624d55ae2514ebfc09271d

Try something like that out.

Suzanne Raphael · 07/16/19

Nice, totally works—the auto check is a great solution, thanks!

I do wish I understood how it works, exactly. Like in common language, what is being said? I'm trying to wrap my head around how to understand and construct these formulas, I have no Excel background in such things, alas.

This is how I'm trying to break it down, seems like what is in bold is saying what I've noted below:

=IF(COUNTIF(CHILDREN(), 1) > 0, 1, 0)

"if this row has children with a checkbox in this column and it is checked"

...but the opening IF, the "greater than" sign and the 0, 1, 0 (false, true, false)....just not quite getting how that is resulting in the parent being auto-checked :-/

I also can foresee issues if a row that is bot a parent and a child has the checkbox manually messed with, as that seems to delete the formula from the cell. Wish there was a way to lock formulas!

Anyway, THANKS AGAIN!

Andrée Starå · 07/16/19

Hi,

=IF(COUNTIF(CHILDREN(), 1) > 0, 1, 0)

If the number of children with a checkbox checked is more than zero > then check the box and if none are checked > then do nothing.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Suzanne Raphael · 07/16/19

Thanks for your reply, Andrée!

So basically this:

IF = If

(COUNTIF(CHILDREN(), = number of children with a checkbox

1) = checked

> 0,1, = is more than zero check the box (in this cell)

0) = else do nothing

Is that right? Doesn't seem quite right :-/

Cheers,

Suzanne

Paul Newcome · 07/17/19

Suzanne,

That is correct. This would go in the parent row of the Fail column to basically say that if at least one child row fails, the parent row will be a fail.

To mark the parent row as Pass if ALL children pass, you would use something like this...

=IF(COUNTIFS(CHILDREN(), 1) = COUNT(CHILDREN(Task@row)), 1)

.

These formulas would go only in the parent rows.

Andrée Starå · 07/17/19

Happy to help!

That is correct! What doesn't seem right?

Suzanne Raphael · 07/17/19

Hi Andrée-

It works fine, I'm just trying to understand how the formulas are built...learn the language, if you will. Thank you for confirming :-)

Cheers,

Suzanne

Suzanne Raphael · 07/17/19

Thanks, Paul. This solution is working for me, with the caveats that 1) when/if someone manually fusses with the checkbox to which the formula has been added, the formula apparently deletes itself; and 2) I am applying the formula to all of the checkboxes in the column to allow for the flexibility that a child could become a parent at any moment (just like in real life, alas). This isn't causing a problem (yet).

My goal is that this be a bullet-proofQA tool that can be easily used by others who are not very familiar with Smartsheet. I've used it quite a bit, but am ashamed to say I haven't invested the time to really understand how to string together formulas, and instead just tend to mimic stuff I find online. I really want to gain a better understanding!

Paul Newcome · 07/17/19

Unfortunately there is no way to preserve the formula if someone manually alters a checkbox.

This will also be true when/if a child becomes a parent row. If a child row has already been manually changed and the formula overwritten, the formula would need to be re-added once the row becomes a parent row.

One option to help with this would be to lock all of the parent rows and make it so that people can only edit child rows.

Andrée Starå · 07/18/19

Excellent!

Happy to help!

Here's an excellent resource for more information about the different functions and their structure: https://help.smartsheet.com/functions

Hope that helps!

Paul Newcome · 07/18/19

There is also a template in the Solution Center that is called "Smartsheet Formula Examples". It is an interactive sheet with sample data in it. It has an example of every function that you can experiment with. If you happen to accidentally save it after messing something up, you can delete the sheet and download a fresh one from the same template. The only catch with this one is that you will need to occasionally download a fresh copy to ensure your examples sheet is completely up to date.

Suzanne Raphael · 07/18/19

Thanks, again Andrée! Really appreciate the swift assistance.

Cheers,

Suzanne

Suzanne Raphael · 07/18/19

Hey Paul, thanks very much. Now that you mention it, I remember that resource, and will seek it out again for sure. I've been away from Smartsheet for a couple years and am hoping to sell it in to my new org...just gotta get my ducks in a row first!

Andrée Starå · 07/18/19

You're welcome!

I'm always happy to help!

Checkbox condition in a child row, conditional formatting of parent?

Comments

Help Article Resources

Categories