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,

 

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?