Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Can you automatically apply a format to parent rows?

SashaR
SashaR ✭
edited 12/09/19 in Archived 2015 Posts

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? 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Dr. Holly Pope
    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. 

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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. 

    · Share on FacebookShare on Twitter
  • Dr. Holly Pope
    Dr. Holly Pope ✭✭

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

    · Share on FacebookShare on Twitter
  • David Destreza
    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!

    · Share on FacebookShare on Twitter
  • Beth Kennedy
    Beth Kennedy ✭

    This is so helpful, thank you!

    · Share on FacebookShare on Twitter
  • ACharvet
    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.

    · Share on FacebookShare on Twitter
  • Sean B
    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?

    · Share on FacebookShare on Twitter
  • Bruno Simoes
    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

    · Share on FacebookShare on Twitter
  • Geoff Kennedy
    Geoff Kennedy ✭
    edited 09/12/16

    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)

    · Share on FacebookShare on Twitter
  • CcHPal
    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?

    · Share on FacebookShare on Twitter
  • Michael Kirzhner
    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. 

     

    · Share on FacebookShare on Twitter
  • Nlarkin
    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!

    · Share on FacebookShare on Twitter
  • teadora
    teadora ✭

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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories