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.

Automating RYG balls based on completed dates in parent row OR children rows

Cathy Holsing
edited 12/09/19 in Archived 2016 Posts

Hi all,

I have a sheet with parent rows where the status column is updated with a RYG ball based on the completion date and due date of the row: 

=IF(ISBLANK([Due Date]108), "", IF(ISDATE([Completed Date]108), "Green", IF(TODAY() - [Due Date]108 > 0, "Red", IF(TODAY() - [Due Date]108 > -7, "Yellow"))))

The children below this parent have the same formulas in their individual rows.  

What I'd like to do now is add the ability to add a blue ball to the parent row based on any child row being completed.  (So if the parent row does not have a completed date entered, blue would show that something is in process based on one of the child tasks).  

I can get this to work to show the blue ball,

IF(COUNTIF(CHILDREN(), "Green") > 0, "Blue")

but I don't know if it is possible to combine the two formulas.  Any help would be greatly appreciated!

 

 

Comments

  • Greg Gates
    Greg Gates ✭✭✭✭✭

    It's definitely possible! You just have to decide what priority the blue status ball should have. Then, you can insert into your nested if-statement accordingly. So, for example, if you think it's more important to show the blue ball than the red/yellow ball you could write your formula like this:

     

    =IF(ISBLANK([Due Date]108), "", IF(COUNTIF(CHILDREN(), "Green") > 0, "Blue", IF(ISDATE([Completed Date]108), "Green", IF(TODAY() - [Due Date]108 > 0, "Red", IF(TODAY() - [Due Date]108 > -7, "Yellow")))))

     

    If you only want to show blue *after* checking to see if it's red or yellow, you could put it at the end:

     

    =IF(ISBLANK([Due Date]108), "", IF(ISDATE([Completed Date]108), "Green", IF(TODAY() - [Due Date]108 > 0, "Red", IF(TODAY() - [Due Date]108 > -7, "Yellow", IF(COUNTIF(CHILDREN(), "Green"), "Blue", ""))))

     

    I hope that's helpful!

  • This works great!  Thanks very much!

This discussion has been closed.