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.
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.
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.
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.
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:
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:
To create a Column Formula column to test if a row has children, your column formula could be written as shown here:
So the result would look like this, which you could then set up conditional formatting on: