Conditional Formating on Parents

tfassnacht

Hello community,

Some of my subtasks are children to a large task (parent in SS).

I would like to find a way to conditional-format all parents-row. (Display them bold for instance).

I cannot find a way to do this with conditional formating ? Am I wrong ? Best,

Find more posts tagged with

Comments

Andrée Starå

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

Paul Newcome

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".

tfassnacht

hi André,

IF(COUNTIF(CHILDREN(), 0) > 0, 0, 1) worked but only for parents not grand-parents.

Any clue ?

THanks.

Andrée Starå

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

Landon Kauffman

hi @Andrée Starå Do you have a solution for multiple levels deep (instead of just one)? thanks!

Andrée Starå

Hi @Landon Kauffman

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!

Landon Kauffman

@Andrée Starå ty!

Andrée Starå

@Landon Kauffman

You're more than welcome!

User251

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.

User251

@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?

Andrew R.

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!

Genevieve P.

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

Andrew R.

@Genevieve P. is there the ability to make that a column formula? Or does it just have to be individually by task name?

Genevieve P.

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

Andrew R.

@Genevieve P. may I be a little dense for a second and ask what the difference between - and / is as a separator?

Sign in to join the conversation!

Quick Links

Trending discussions

Sheet permissions
Does a person have to be granted access to a sheet in order to complete an automated request? I was under the impression that anyone could respond to an automated request, but I just had to share a sheet with someone in order for them to complete their task. Thanks for your help
Automating Across Sheets
Hello, Anyone up for a complicated challenge lol. I have two sheets: "Account Tracker" and "Portal Issues" If we run into an issue gaining portal access to an account, we log it on the Portal Issues sheet. We track progress there and once we have portal access, we check the "Resolved" column manually. We then also manually…
Smartsheet Notifications
I'd like to get notified when a new smartsheet or workspace is created for the entire organization. Along with a weekly update of what's been updated. How can I do this? I am managing ~100 Smartsheet's and I need the big picture incase someone starts something without overseeing and including them to roundtable…