need pls. advise for formula

Options

Hi, pls. see pic. below: I'm looking formula that will light in green column called greenlight to install , when all other columns are checked and red when at least 1 column is unchecked

Thanks in advance

Yuval


Best Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Yuval, 

    You can try this formula for your condition- 

    =IF(AND([Materials in Rigotec/Talad]@row = true, [Materials in CR]@row = true, [HC in Place]@row = true, [QA/QC done before install]@row = true), "Green", IF(AND([Materials in Rigotec/Talad]@row = false, [Materials in CR]@row = false, [HC in Place]@row = false, [QA/QC done before install]@row = false), "", IF(OR([Materials in Rigotec/Talad]@row = false, [Materials in CR]@row = false, [HC in Place]@row = false, [QA/QC done before install]@row = false), "Red"))) 

    Hope this will work for you 

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Yuval
    Yuval ✭✭✭
    Answer ✓
    Options

    Cool, many thanks Kaveri, working as watch, just copy and paste, perfect

    Yuval

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Yuval, 

    You can try this formula for your condition- 

    =IF(AND([Materials in Rigotec/Talad]@row = true, [Materials in CR]@row = true, [HC in Place]@row = true, [QA/QC done before install]@row = true), "Green", IF(AND([Materials in Rigotec/Talad]@row = false, [Materials in CR]@row = false, [HC in Place]@row = false, [QA/QC done before install]@row = false), "", IF(OR([Materials in Rigotec/Talad]@row = false, [Materials in CR]@row = false, [HC in Place]@row = false, [QA/QC done before install]@row = false), "Red"))) 

    Hope this will work for you 

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Yuval
    Yuval ✭✭✭
    Answer ✓
    Options

    Cool, many thanks Kaveri, working as watch, just copy and paste, perfect

    Yuval

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!