Can you automatically apply a format to parent rows?

SashaR

I would like parents rows to be formatted bold automatically in my sheet. I looked into conditional formatting but it did not have an option to reconize parent rows. Is there anyway to do this? 

Dr. Holly Pope

I would also like this feature. I do have a work around. I added a check box column "Header" and then applied conditional formatting to Bold and give background color if the header column is checked.  

Hope this helps a little. 

Travis

Dr. Holly Pope's suggestion can be slightly modified to make this process automated. You could add a formula to the checkbox column that will automatically 'check' if the corresponding row has more than 0 children (which would make it a parent). Using this formula with conditional formatting will automatically format parent rows in your sheet.

 

This formula will can be added to a checkbox cell and will check if the referenced cell has more than 0 children:

 

=IF(COUNT(CHILDREN([Task Name]1)) > 0, 1)

 

In this example, “[Task Name]1” is referencing the Primary column cell in the row (Primary columns are identified as the only column with bold header text). This cell will need to be updated in your sheet to reference the correct cell.

 

Once you have this formula set up in your sheet, drag fill it down to copy it on all your rows. With autofilling formulas, any new row you add will contain this formula automatically. 

Dr. Holly Pope

Thanks Travis!! This is wonderful to know. I will definately be adding the formula to my sheets! 

David Destreza

Greetings Community!

I've sent an email to Austen at Smartsheet about this same thing.  He told me that he would pass this on. 

 

Best to you all!

Beth Kennedy

This is so helpful, thank you!

ACharvet

It would be nice to have an outline level, similar to what MS project has.  This would allow you to filter tasks by level, and also a value that could be used for conditional formatting, or WBS numbering.

Sean B

This is really helpful. Thanks, everyone. So I added this formula to automate it and it works fine. However, what I thought I could do was to drag the formula down the column and watch each row thats a parent row get checked automatically (based on the formula) and then get formatted, based on my conditional format. Unfortunately what I see is #UNPARSEABLE. Am I doing something wrong?

Bruno Simoes

Since Aug 06, they've released the "Ancestors()" to check the number of ancestors. This solved my problems to check for multiple levels of children like this (I use 5 levels of tasks).

 

This function checks to see if the task if a child (hence no formatting is applied), else it checks the number of ancestors (with "zero" being the parent of all parents)

=IF(COUNT(CHILDREN(Task1)) = 0, "", IF(COUNT(ANCESTORS()) < 1, "Cheio", IF(COUNT(ANCESTORS()) < 2, "Três quartos", IF(COUNT(ANCESTORS())<3, "Meio", "Um quarto"))))

 

Works like a charm

Geoff Kennedy

Amended

 

You can use the Ancestors function to calculate a "Level" then use conditional format to format each level (based on whether it is Level 1,2,3 etc)

 

Formula is basically

==COUNT(ANCESTORS([Task Name]1)) + 1

 

ie 

Level 1 (top level) may or may not have children but is top level (ie could be Red Background Bold 10 Point Font)

Level 2 is indented once (ie could be Bold 8 Point Font)

Level 3 idented three times (ie plan 8 point font)

CcHPal

Is it possible to create a formula that would automatically highlight any child rows as they are created or based on a check or particular term in a column of the parent row?

Michael Kirzhner

I am also using a reverse of CHILDREN, ANCESTORS to figure out how many levels are above a specific row. That does not have the same duplication as CHILDREN as you do not know how many are SIBLINGS. 

 

Nlarkin

Genius Bruno. 

Here is my version with english identifiers and going to the 10th sub level:

=IF(COUNT(CHILDREN([Task Name]1)) = 0, "", IF(COUNT(ANCESTORS()) < 1, "Top Task", IF(COUNT(ANCESTORS()) < 2, "1st sub Group", IF(COUNT(ANCESTORS()) < 3, "2nd sub group", IF(COUNT(ANCESTORS()) < 4, "3rd sub group", IF(COUNT(ANCESTORS()) < 5, "4th sub group", IF(COUNT(ANCESTORS()) < 6, "5th sub group", IF(COUNT(ANCESTORS()) < 7, "6th sub group", IF(COUNT(ANCESTORS()) < 8, "7th sub group", IF(COUNT(ANCESTORS()) < 9, "8th sub group", IF(COUNT(ANCESTORS()) < 10, "9th sub group", "10th sub group")))))))))))

Well done!

teadora

This is super helpful. Has Smartsheet done anything to improve upon conditional formatting of levels since 2016?