PARENT Rows - Conditional Formatting

Is there a way to apply conditional formatting to PARENT rows and to have that vary depending on what heirarchy level the parent has, e.g Level 1, Level 2, Level 3 separate conditional formatting?

If this is not natively enabled (doesn’t seem to be) can one achieve it via a “Helper Column” which can use some heirarchy function to determine the level a row sits at and and set the conditional formatting to change based on that cell’s value? This is how one does it in Smartsheet.

Thanks

2 Likes

Welcome @Caleb_Clapp!

There is not currently a way to apply conditional formatting based on level in hierarchy, but this is a great idea and one we will consider for the roadmap. Will update this thread when we have more to share. Tracking this as issue #004782.

Hi Matt

Is there a way to have a cell that gets a value from an formula based on its level in the heirarchy, or at least if it has a parent or if it has children rows? If so I can use result as the basis of the conditional formatting, (and then hide these “helper columns”). i.e if the row has a child but no parent formatting would be set. In my example, I would use two columns, 1 for if has child and 2nd for if has parent. Do any such formulas exist? I note there are several heirarchy formulas.

Yes, I would suggest taking a look at the hierarchy formulas documented here: https://support.spreadsheet.com/hc/en-us/articles/360031049231#h_2f50159e-3f0b-463e-bdd8-8f47a49c67a2

…and in function-specific articles here:
https://support.spreadsheet.com/hc/en-us/sections/360004915031-Hierarchy-functions

Thanks again, Matt

Since I am a new user and after reading the heirarchy formulas in the two links you provided (and previously) I do not understand how to determine the below… Are you able to assist with the formulas that will determine:

a) if the row has children?
b) if the row has parents?

I would put the formulas in 2 separate columns. Then for the conditional formatting I would reference the columns and use “Is” or “Is not” Blank (or some other consistent value eg “True” / “False” ) depending on what the formula you give yields.

Thanks for your help.

Hi Caleb,

Sure, there are multiple ways to do this.

METHOD 1: Cell-level formulas
You can write cell-level formulas to test whether a row has a parent or children, and then copy those formulas into each row where you need to calculate this.

To test if a row has a parent, you can write a formula like:
=IF(PARENTCELL(A2)="",FALSE,TRUE)

To test if a row has one or more children, you can write a formula like:
=IF(COUNTA(CHILDCELLS(A2))>0,TRUE,FALSE)

You can then apply these same formulas in other cells within your column, resulting in something like this, which you could then set up conditional formatting on:

image

METHOD 2: Column formula columns
A perhaps easier solution is to use a Column Formula.

To create a Column Formula column to test if a row has a parent, your column formula could be written as shown here:

image

To create a Column Formula column to test if a row has children, your column formula could be written as shown here:
image

So the result would look like this, which you could then set up conditional formatting on:

image

4 Likes

Hi Matt

That is extremely helpful and I never would have gotten there without your help. Thanks very much!

Likely others will find this useful as a workaround until parent/child status is implemented as a condition for conditional formatting.

All the best.

1 Like