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

Options
marym
marym ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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.

 

 

 

 

Comments

  • steven.reed14881
    steven.reed14881 ✭✭✭✭✭
    Options

    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.

     

     

     

    Configuring Blank Date Conditional Formatting.JPG

  • steven.reed14881
    steven.reed14881 ✭✭✭✭✭
    Options

    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

    Configuring Blank Date Conditional Formatting v2.JPG

  • marym
    marym ✭✭✭✭
    edited 09/12/17
    Options

    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
    marym ✭✭✭✭
    Options

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

This discussion has been closed.