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.
Color coding based on multiple columns
Hi,
I have a sheet that includes Start Date, Finish Date and Status Columns. I'd like to create an IF formula that considers data in all three columns in order to create color coded balls as shown here (https://www.smartsheet.com/blog/support-tip-automate-RYG-balls).
Specifically, I'd like the logic to be:
If Start is after TODAY, "Blue"
If Start is before TODAY and status is "Not Started", "Yellow"
If Finish is after TODAY and status is "In Progress" or "On Track", "Green"
If Finish is before TODAY and status is not "Complete", "Red"
If Finish is before TODAY and status is "Complete", "Green"
I think its possible to apply this kind of nested logic in Excel using AND and OR functions, but I can't make it work the same way in SmartSheet.
Thanks!
Start | Finish | Today | Status | Ideal Result |
9/1/2015 | 12/1/2015 | 12/3/2015 | Complete | G |
11/5/2015 | 1/27/2015 | 12/3/2015 | In Progress | G |
11/7/2015 | 1/20/2016 | 12/3/2015 | Not Started | Y |
12/30/2015 | 2/27/2016 | 12/3/2015 | Not Started | B |
11/5/2015 | 1/27/2016 | 12/3/2015 | On Track | G |
10/4/2015 | 11/3/2015 | 12/3/2015 | On Track | R |
Comments
-
Try this equation:
=IF(Start1 > TODAY(), "Blue", IF(AND(Start1 < TODAY(), Status1 = "Not Started"), "Yellow", IF(AND(Finish1 > TODAY(), OR(Status1 = "In Progress", Status1 = "On Track")), "Green", IF(AND(Finish1 < TODAY(), Status1 = "Complete"), "Green", "Red"))))
If you look at the equation closly I used your logic in the same order you listed it, however your forth line i swapped between the 4th and 5th lines. Esentially making the last result "Red" if none of the other possibilites occured.
Here is an image of my output.
Good Luck...
-
Nice one Joel!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 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