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

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    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.

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Vinton Douglas

    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

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    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?

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    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?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Vinton Douglas

    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!