# Looking for a formula to check mark children, UNLESS that child has it's own (grandchildren)

So I'm working on a formula to check checkboxes in a column if the primary column (named "Task Name") is a child UNLESS that child has it's own children.

Basically what I have a is a job tracker, and some jobs are simple. Some have are more complex broken into parts. For example the completion of job "Alpha" is a sum of 3 parts; "run 1", "run 2" and "run 3". So "Alpha" being the parent is not checked, but it's administrative so it has it's own tasks, then it has grandchildren tasks for "run 1", "run 2, etc.

Might look something like this, in the column "Task Name":

Job: Alpha

1. Quote
2. Procurement
3. Engineering
4. Scheduling
5. "Run 1"
1. CNC
2. assemble
3. pack
6. "Run 2"
1. CNC
2. assemble
3. pack

So tasks 1-4 would be checked, then a,b, c of each Run. But NOT the job name "Alpha", nor 5, 6, 7. I hope that makes sense.

My formula so far is:

=IF(COUNT(ANCESTORS()) > 0, 1, 0)

Which leaves the Parents (such as "Alpha") unchecked, but checks all of the "Runs"

I'm ultimately going to do is hide that column, but use it to run a daily report of job tasks that are running close to deadlines, flags delays, etc. (there's a lot of other information on my tracker that I wont bore you with). This allows me to address roadblocks, triage where necessary and share out with my team a status check of where we're at. Especially since we often times have different jobs running at once and it gets a little chaotic.

So if "Run 2" is due out soon, but "Run 1" is behind schedule. It'll flag that as a need to be addressed; why are we behind? Is it a material issue? Equipment? Etc.

I'm also open to suggestions to a better way to do this.

Thank you

• ✭✭✭✭✭✭

Hi Chris,

Try something like this to get the Level and then you can use it to decide what should me selected or not.

```=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)
```

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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 Chris,

Try something like this to get the Level and then you can use it to decide what should me selected or not.

```=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)
```

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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.

• edited 07/09/20

Thanks for the quick response Andrée. I plugged that in, and it works to check all of the parents (which works too, for my needs) but under "Run 1", "Run 2", "Run 3" it's returning #BOOLEAN EXPECTED.

• ✭✭✭✭✭✭

Happy to help!

You'd use my formula to get the different levels in a so-called helper column and then you'd use that to check the boxes.

You're getting the error because the checkbox column is expecting a 0 or 1.

Make sense?

Would that work?

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.

• Gotcha, gotcha.

K, I plugged it into a helper column and see what you mean. I think that I can work with this. Let me play with this a little bit.... THANKS!

• Andree! Thank you! This will work perfect.

To consolidate/simplify for others. I now have a column called "CheckedParent" and "CheckedParentHelper", which are both hidden. In the "CheckedParentHelper" I have Andree's formula (with "Task Name" being my primary column name):

=IF(COUNT(CHILDREN([Task Name]1)) > 0, COUNT(ANCESTORS()) + 1)

This yields a "1" for all Parents and "2" for all Children that have their own Children.

Then in my "CheckedParent" column (which is a checkbox column), I have the following formula:

=IF(COUNT(CheckedParentHelperColumn1) > 0, 1, 0)

For those who might be a little lost, "1" = checked, "0" = unchecked. And you can just double click on the checkbox cell to insert your formula. Then drag that little blue square down your entire column (new rows will inherit the formulas) for both "CheckedParent" and "CheckedParentHelper"

Now I can run a report that looks for all of the checked rows with check boxes and compares them against their anticipated finish dates that are 3 days out (or behind)

• ✭✭✭✭✭✭

Excellent!

You're more than welcome!

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 Chris,

this formula,

```=IF(COUNT(CHILDREN(Task@row)) > 0, COUNT(ANCESTORS()) + 1)
```

provides the level, but leave the level blank for the last level. I mean it does not give the level for those task that has no children. How I get level for all?

• ✭✭✭✭✭

Hi Chris,

I have used this easier formula, which gives the level

=COUNT(ANCESTORS()) + 1

• Right on

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!