RAG Changes to Monitor Cooler and Freezer
Hi All-
I have a single dropdown to monitor the temperatures of 2 Coolers and 2 Freezers from incoming forms submitted.
Cooler 1
Cooler 2
Freezer 1
Freezer 2
If the temp for coolers are <=40F, I want the status bar to return green, otherwise red
if the temp for the freezer <=0F, I want the status bar to return green, otherwise red
Is it possible to achieve the results on one incoming spreadsheet?
Best Answers
-
Hi @Vinton Douglas,
Yes , it is possible. Try this sheet with below formula:
=IF(CONTAINS("Cooler", Products@row), IF(Temperature@row <= 40, "Green", "Red"), IF(CONTAINS("Freezer", Products@row), IF(Temperature@row <= 0, "Green", "Red")))
Hope that helps.
Gia Thinh Technology - Smartsheet Solution Partner.
-
In fact, the formula should be working for Cooler 3/4/5/... or Freezer 3/4/5/... because we just check the Cooler text or Freezer text in the Products column.
Can you post your screenshot so that we can check it out?
Gia Thinh Technology - Smartsheet Solution Partner.
-
I hope you're well and safe!
Try something like this.
=IF([Temperature (F)]@row = "", "", IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Freezer", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row >= "-40", "Green", "Red")))))
Did that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi @Vinton Douglas,
Yes , it is possible. Try this sheet with below formula:
=IF(CONTAINS("Cooler", Products@row), IF(Temperature@row <= 40, "Green", "Red"), IF(CONTAINS("Freezer", Products@row), IF(Temperature@row <= 0, "Green", "Red")))
Hope that helps.
Gia Thinh Technology - Smartsheet Solution Partner.
-
Thank you very much for your response. It works great! I tried adding another cooler and freezer but its not working. Could you add another aspect to the formula if I want to add Cooler 3 and Freezer 3?
-
In fact, the formula should be working for Cooler 3/4/5/... or Freezer 3/4/5/... because we just check the Cooler text or Freezer text in the Products column.
Can you post your screenshot so that we can check it out?
Gia Thinh Technology - Smartsheet Solution Partner.
-
Hi Gia,
Thanks much. I created the nested formula and it works great. The only issue is if this is populated on a spreadsheet, the RAG status reflects green or red even if the space under temperature is blank. See formula:
=IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Freezer", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row >= "-40", "Green", "Red")))...
How can I tweak the formula so that when the temperature is blank, the RAG status remains blank?
-
I hope you're well and safe!
Try something like this.
=IF([Temperature (F)]@row = "", "", IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Freezer", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row >= "-40", "Green", "Red")))))
Did that work?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!