# RAG Changes to Monitor Cooler and Freezer

Options

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?

• ✭✭✭✭✭✭
Options

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 Co., LTD - Smartsheet Solution Partner.

• ✭✭✭✭✭✭
Options

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 Co., LTD - Smartsheet Solution Partner.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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 Co., LTD - Smartsheet Solution Partner.

• Options

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?

• ✭✭✭✭✭✭
Options

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 Co., LTD - Smartsheet Solution Partner.

• Options

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?

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!