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
- Quote
- Procurement
- Engineering
- Scheduling
- "Run 1"
- CNC
- assemble
- pack
- "Run 2"
- CNC
- assemble
- 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
-
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
✅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!
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.
Answers
-
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
✅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!
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 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)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!