Parent row formatting based on Child row action
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.
Best Answer
-
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"
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
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])
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
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?
-
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"
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
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
-
Thank you so much Mark! This worked perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!