Conditional Formating on Parents
Comments
-
Hi,
You need a helper column for that. Add the formula below to a checkbox column and use that in Conditional Formatting.
=IF(COUNTIF(CHILDREN(), 0) > 0, 0, 1)
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
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.
-
I personally use...
=IF(COUNT(ANCESTORS([Task Name]1)) < COUNT(ANCESTORS([Task Name]2)), COUNT(ANCESTORS([Task Name]1)))
Once this is entered into row 1, you can dragfill on down the line. This will assign a number based on the level of hierarchy. That number can then be used in conditional formatting.
In the example below, my helper column is called "Parent".
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
hi André,
IF(COUNTIF(CHILDREN(), 0) > 0, 0, 1) worked but only for parents not grand-parents.
Any clue ?
THanks.
-
It should be a COUNT and not COUNTIF.
Sorry about that!
Try something like this.
=IF(COUNT(CHILDREN()) > 0, 1, 0)
Did it work now?
Best,
Andrée
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 Starå Do you have a solution for multiple levels deep (instead of just one)? thanks!
-
I hope you're well and safe!
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)
Did that work/help?
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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.
-
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 @Andrée Starå . Can you please translate the function "=IF(COUNTIF(CHILDREN(), 0) > 0, 0, 1)" ..How does that formula help with conditional formatting? I tried it and converted it to a column formula but I had 1s for both the parent and the children.
-
@Andrée Starå The second formula worked
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)
if the task name has children, then assign the number 1 to the parent. Right? I understand that you are using Ancestors because there is more than one level. otherwise, we can use PARENT. correct?
-
To add onto this, what if I want to have the child tasks take on the color of the parent, where there are different colors for each project (I treat them as labels and do this in the Card view to make it Kanban style for ease of visuals). So, for example, I have:
Project A (Red label)
- Task 1
- Sub-task 1
- Sub-task 2
- Sub-task 3
Project B (Blue label)
- Task 1
- Sub-task 1
- Sub-task 2
- Sub-task 3
What would the helper formula look like in that case, and how would I apply it to conditional formatting? Thanks!
- Task 1
-
Hi @Andrew R.
In this instance what I would do is simply use the ANCESTORS function to return all the Parent text into one helper cell, like so:
=ANCESTORS([Task Name]@row)
Then you can set your Conditional Formatting rule to be if that helper column contains the phrase "Project A", and a second rule if it contains "Project B", and so on.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. is there the ability to make that a column formula? Or does it just have to be individually by task name?
-
Hi @Andrew R.
Yes, this can be turned into a column formula. You'll want to reference the task name cell, versus typing in a task name.
However!!!! I completely missed a whole portion of this formula, my apologies! You'll need to wrap this in a JOIN function to tell it what to do when there's more than one parent:
=JOIN(ANCESTORS([Task Name]@row), " - ")
You can swap out the " - " to be a different separator:
=JOIN(ANCESTORS([Task Name]@row), " / ")
Keep in mind if you have a different column name where your tasks are stored, you'll want to reference that column instead:
=JOIN(ANCESTORS([Column Name]@row), " / ")
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. may I be a little dense for a second and ask what the difference between - and / is as a separator?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives