#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Project Sheet: Formatting Parent Row based on Children

✭✭✭✭
edited 12/09/19

Does anyone know a way through formula and formatting rules to highlight a parent row cell (finish) if any of its child rows have a blank value in finish?

We want to avoid the parent finish date looking deceiving in reports when only some of the children finish dates are known.

Tags:

• ✭✭✭✭✭

It is a little bit of a roundabout solution, but I have figured out how to achieve this. There are a few steps involved:

1. Insert a checkbox column (that you will be able to hide from viewers) titled 'Summary' with this formula in the column: =IF(COUNT(CHILDREN()) > 0, 1, 0). This will flag any tasks that contains CHILDREN tasks.
2. Insert a Text/Number column (again that you will be able to hide from viewers) titled 'Blank Dates' with this formula in the column: =IF(Summary1 = 1, SUM(CHILDREN()), (COUNTIF(Start1:Finish1, ""))). This will perform one of two functions--depending on the value in 'Summary':
• if 'Summary' is not checked: it will count the number of blank Start or Finish Dates on each task line.
• if 'Summary' is checked: it will calculate the sum of all of its CHILDREN tasks.
3. Then create a two layered Conditional Formatting arrangement that looks at the following conditions:
• Summary is Checked
• Blank Dates is not equal to 0

Below is a screenshot of my worksheet where I developed this solution. Let me know if there are any questions about this.

• ✭✭✭✭✭

Mary,

As I posted my solution, I also realized that you could take this solution one step further. Rather than just setting the conditional formatting to color the cell (or entire row) when it did not equal 0, you could set different thresholds to trigger different colors. This could designate the severity of missing dates, as shown in the screenshot below. Maybe more than you need, but just popped into my mind.

Steve

• ✭✭✭✭
edited 09/12/17

Steve,

Thank you!  This is awesome.  I will work on implementing this today and let you know how it worked out.  Really appreciate what you've done!

Mary

• ✭✭✭✭

Thanks, Steve,  This worked perfectly!   Sorry, forgot to tell you that sooner!

This discussion has been closed.