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

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/09/20 Answer ✓

    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!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!