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?
Comments
-
I am looking for a similar solution, in my post too. Thanks for posting this.
Cuatro
-
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.
-
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!
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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.
-
Happy to help!
That is correct! What doesn't seem right?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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!
-
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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Thanks, again Andrée! Really appreciate the swift assistance.
Cheers,
Suzanne
-
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!
-
You're welcome!
I'm always happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!