Check off category when all sub categories have been checked

Discitus

Hello, I'm still new at Smartsheet so need a little bit of guidance.

I think in this situation I need to use an IF formula and not conditional formatting. I want to add a rule that if all my sub-categories have been checked off, my category than be check off. Same rule would apply to a higher level, once all category have been check off my main category would be check.

Adam Overton

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), true), true, false)

 

Discitus

Thanks Adam for your quick reply. It works but not in all circumstances, maybe you can also guide me through this one.

I have 3 level of check box. (main categories (1st level), categories (2nd level) and sub-categories(3rd level).

I can apply the rule you shared with me when all 3rd level are checked, 2nd automatically get check off = perfect so thank you. But then if I want to have my 1st level check off as well when all 2nd level has been checked it doesn't work. I can only apply the rule you shared in one level. Do i need to use a different formula for my 1st level to generate a check mark when 2nd level are checked off? And note I wish to keep the rule from my 3rd level. So when 3rd level is manually checked off 2nd level generate a check mark, once all 2nd level have been check, it generates a check mark in my 1st level. Does it make sense haha

Also, I notice if ever someone were to check the box of my 2nd level that is automatically generated I loose my formula, it that typical?

 

Adam Overton

I probably won't have time to investigate further today, but a quick test seems to have it working for me. Can you look again? Otherwise hopefully another community member will be able to step in on this today.

Yes, if they check it, unfortunately it will overwrite the formula. 

Discitus

Adam, I see the issue I had. 

So the form works but when I was testing it out, I was checking all my 2nd level to see if my 1st level category would get checked off. Instead of checking off all 3rd that then generated checks mark for my 2nd level and once those are all checked, it generates a checks off in my 1st level. As I notice in another question, if I go directly check off the 2nd level, I lose my form that i used in my previous level (3rd).

Is there a way I can freeze my 2nd level box so people don't check the 2ns level box by mistake and then i lose my form data?

Adam Overton

You can lock the row, and then only the sheet owner and admins can modify it.