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

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?

Find more posts tagged with

Formulas

IT & Operations

Product Development

Conditional Formatting

Project Management

Comments

Cuatro

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

Cuatro

mkingsbury

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

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å

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

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

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å

Happy to help!

That is correct! What doesn't seem right?

Suzanne Raphael

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

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

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å

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

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

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

Cheers,

Suzanne

Suzanne Raphael

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å

You're welcome!

I'm always happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?