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
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
-
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives