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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/11/24 Answer ✓

    @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"

    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

  • Mark.poole
    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])

    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?

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/11/24 Answer ✓

    @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"

    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.

  • Brandon Sills
    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

  • Thank you so much Mark! This worked perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!