IF Statement and RYG text values

Jose Munoz
Jose Munoz ✭✭✭✭
edited 05/25/21 in Formulas and Functions

Im running a routine to identify when a record changed for assets and flag it based as follow

If the Values on Current Status and New Status are the same then GREEn

If the Values arent the same RED

if Current Status has any value and New Status is equal #NO MATCH then I wanted to be Yellow but it is not working no matter how I try. the only working formula I have is the one below that if the value is equal then is GREEN otherwise red .

=IF([Current Status]@row = [New Status]@row, "Green", "Red")

Any help will be appreciated


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/24/21

    Try something like this...

    =IF([Current Status]@row = [New Status]@row, "Green", IF(AND([Current Status]@row <> "", ISERROR([New Status]@row)), "Yellow", "Red"))

  • Mike Raposo
    Mike Raposo ✭✭✭✭

    Hi Josem,

    I believe the solution here is to insert your current formula in an IFERROR operator where if the formula returns an error such as #INVALID VALUE then it should return "Yellow", see below for an example that should work:

    =IFERROR(IF([Current Status]@row = [New Status]@row, "Green", "Red"), "Yellow")

    Hope this helps! Let me know if you have additional questions.


    Best,

    Mike

  • Jose Munoz
    Jose Munoz ✭✭✭✭

    Thank you guys for the quick response

    Paul your formula still gives me the #INVALID error

    Mike, yours worked thanks



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!