Conditional formatting based on location in hierachy

Christopher Grinde

Please consider incorporating a functions level() and has_cildren() in the conditional formatting and reporting filters. 

level() would give "0" if top level and "1","2" ... depending on the number of indentations.

has_children() retruns true or false.

 

Combined this would allow:

- making filtered reports only extracting entries without children (while this can be done today, it is cumbersome when you need to implement such a change to 50 existing lists. )

- consistent formatting across sheets as one can use the conditional formatting to for instance give all level 0 blue background and white text, all level one entries grey background on so on. To avoid formatting regular entries, the conditional formatting would only be applied to entries without children.

 

This would ease keeping plans clean and consistently formatted.  

Kennedy Stomps

Hi Christopher--

 

We just implemented a function that I think will accomplish this purpose for you! You could use our new ANCESTORS() function to determine the hierarchy level of each row, then use Conditional Formatting rules to apply formatting based on the results of the function. You'll want to create a new column in your sheet, then add a ANCESTORS() function referencing your Primary column:

 

=COUNT(ANCESTORS([Primary Column]1))

 

This will count the number of parent row ancestors of a row, which will tell you how deeply nested in a hierarchy a row is. If the result is "blank", the row is the highest parent row. A value of "1" would mean the row has one parent, "2", a parent and a grandparent, and so on. You could then set up Conditional Formatting rules for each number needed. 

 

In response to your second question, about a has_children() function, you could probably accomplish this using the existing CHILDREN() function and a COUNT() function. In a new column, you can place a formula like:

 

=IF(COUNT(CHILDREN([Primary Column]1)) > 0, "Parent")

 

This will check if a row has any children and if so, will insert the text "parent" into the cell. You could modify this to check a box, display a symbol, or many other options. Then, you could base your Conditional Formatting off of this result. Hope these options help!