Red / Yellow / Amber formula
Hi,
Can anone please suggest for my sheet that is formatted as shown in the attached image and for the following conditions
- if risk/issues cell is not blank and due date is in the future then yellow
- if past due date then red
- if past due date and with risk/issue cell not blank still red
- if not past due date and no risk/issues text = green
- if a project is status complete it should turn green (regardless of other things if possible)
Answers
-
Hi @sahilhq ,
You need to apply conditional formatting. You can search for the smartsheet tutorial. It's very simple to do. You just create a rule for each. When the rule is met it triggers the format response. You can format specific cells or the row.
Good luck.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi @sahilhq
Conditional formatting as Mark suggested would work well if you're looking to colour the background of the cells... however it sounds like you may be wanting to automate the status ball in your "Project Health" column, is that correct?
If so, you can build a Nested IF statement to achieve this! I'll go through each of your logic points below to show when it will turn each colour, then we'll put it all together in one final formula at the end.
Keep in mind that Logic Formulas start by looking through each statement left-to-right and stop as soon as the criteria is met. Because of this, we can arrange the order of your instructions to first check if the status is complete, and only change to other colours if it's not complete.
Note: this formula presumes your Risk/Issues column is titled "Risk/Issues" (it's not in the screen capture to verify this).
Green
- if a project is status complete it should turn green (regardless of other things if possible)
=IF(Status@row = "Complete", "Green",
Red
- if past due date then red
- if past due date and with risk/issue cell not blank still red
IF([Due Date]@row < TODAY(), "Red",
Yellow
- if risk/issues cell is not blank and due date is in the future then yellow
Since you've already specified that any due dates in the PAST will be red, you don't need to put this in your yellow statement. Instead, you can just look for the "Risk/Issues", and it will automatically also look to see if the due date is Today or in the Future.
IF([Risk/Issues]@row <> "", "Yellow",
Green again
- if not past due date and no risk/issues text = green
The rules above cover what happens if the date is in the past or if the Risk/Issues column has content, so all we need to do is to say that the default colour is green:
"Green"
FULL FORMULA
=IF(Status@row = "Complete", "Green", IF([Due Date]@row < TODAY(), "Red", IF([Risk/Issues]@row <> "", "Yellow", "Green")))
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Great answer @Genevieve P . Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!