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!

Christopher Grinde

I've used the children() fundtion to achive the same. However, the point being is that the functionality should be incorporated in the filters and available directly from the conditional formatting to avoid having to generate a ton of extra columns. 

 

Christopher

Doug Carr

I agree completely with Christopher's comments. In fact, all of the formulas should be available in all places where criteria can be specified. The copying of formulas in Sheets is fragile at best and relies too heavily on users adding rows below existing rows of data or inserting between rows. This breaks when a user skips a row and then the formula does not get copied. This can (and does) easily break the functionality of the sheet and any reports that rely on it. If the intent is to keep the function out of the report criteria and conditional formatting criteria then there should be calculated columns where the formula goes in the column's properties and not in the individual cells where it is subject to the end user changing it or skipping rows.