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
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!