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.