Parent row formatting based on Child row action

Todd O

I am looking for a way to highlight a cell in the parent row if a specific date field in the child row is not blank.

Mark.poole

@Todd O

In your helper column add this formula

=IF(COUNT(ANCESTORS(Test@row)) = 1, "", COUNTIF(CHILDREN(Date@row), <>""))

Changed to match your Rows. Then you can create conditional formatting based on if your helper row has a 1 or not. if you would rather it be a flag just change the column to a flag. 1 will be flagged. 0 will be unflagged. you can of course change the formatting to target specific columns instead of the whole row as well. "Cells"

Mark.poole

@Todd O

The easiest way to accomplish this is to populate the Parent cell with the Child cell. Then you can use conditional formatting to highlight both the parent and child cells. If this is not an option you could create a helper column to achieve this.

=Children([Cell refrence])

Todd O

Thank you. The first option (populating the parent cell with the child cell) is not an option. I started working on a helper column but have not figured out how to achieve the goal. Can you please elaborate?

Mark.poole

@Todd O

In your helper column add this formula

=IF(COUNT(ANCESTORS(Test@row)) = 1, "", COUNTIF(CHILDREN(Date@row), <>""))

Changed to match your Rows. Then you can create conditional formatting based on if your helper row has a 1 or not. if you would rather it be a flag just change the column to a flag. 1 will be flagged. 0 will be unflagged. you can of course change the formatting to target specific columns instead of the whole row as well. "Cells"

Brandon Sills

Another way to do this with helper columns is:

Create one column to distinguish the parent/child relationship… I call mine [Hierarchy Helper]…. Children = 1, Parent = 2 etc etc for how many Indents….

Create another helper column as a checkbox and insert this column formula:

=IF(AND([Hierarchy Helper]@row = 1, ISBLANK([Specific Date]@row)), 1, IF([Hierarchy Helper]@row = 2, COUNTIF(CHILDREN([Checkbox Column]@row), 1), ""))

Then add conditional formatting, if [Hierarchy Helper] is 2…then add an "AND" condition for the checkbox to be checked… etc.

The Hierarchy Helper is also very beneficial in the sheet when you want to maintain column formulas but need a rolled up value for the parent rows..

Both of these responses would work for your use case.

Brandon

Todd O

Thank you so much Mark! This worked perfectly!