Project Sheet: Formatting Parent Row based on Children

marym

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.

 

 

 

 

steven.reed14881

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.

 

 

 

steven.reed14881

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

marym

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

marym

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