IF / AND / OR WORKS ON EXCELL BUT NOT ON SMARTSHEET

Dan Benitah

Hello Everyone,

I have a formula I am working with, that is working just fine on excell but not in smartsheet, it shows #UNPARSEABLE, PLEASE HELP !!

HERE IS THE FORMULA :

=IF(AND(ISBLANK(J5),ISBLANK(J4)),J3,IFS(OR(J3="red",J4="red",J5="red"),"red",OR(AND(J3="green",J4="green",OR(J5="green",ISBLANK(J5)))),"green",OR(AND(OR(J3="yellow",J4="yellow",J5="yellow"),J4<>"red",J5<>"red")),"yellow"))

KPH

Hi @Dan Benitah

There is not an IFS function in Smartsheet. You will need to re-write the function using nested IF functions to replace this part:

IFS(OR(STATUS7="red",STATUS8="red",STATUS9="red"),"red",OR(AND(STATUS7="green",STATUS8="green",OR(STATUS9="green",ISBLANK(STATUS9)))),"green",OR(AND(OR(STATUS7="yellow",STATUS8="yellow",STATUS9="yellow"),STATUS8<>"red",STATUS9<>"red")),"yellow")

Nested IF means replacing the value if false with a second IF. Taking this:

=IF( logical_expression, value_if_true, value_if_false)

And doing this:

=IF( logical_expression, value_if_true, IF( logical_expression, value_if_true, value_if_false))

The first part of your formula is fine

=IF(AND(ISBLANK(STATUS9), ISBLANK(STATUS8)), STATUS7)

The red part would become

=IF(OR(STATUS7 = "red", STATUS8 = "red", STATUS9 = "red"), "red")

The green part would become

=IF(AND(STATUS7 = "green", STATUS8 = "green", OR(STATUS9 = "green", ISBLANK(STATUS9))), "green")

The yellow part would become

=IF((AND(OR(STATUS7 = "yellow", STATUS8 = "yellow", STATUS9 = "yellow"), STATUS8 <> "red", STATUS9 <> "red")), "yellow")

If you nest them in this order the yellow part is only evaluated if all the other statements are false, the green part is only evaluated if the first part and the red part are false, the red part is only evaluated if the first part is false.

This is how the nest would look

=IF(AND(ISBLANK(STATUS9), ISBLANK(STATUS8)), STATUS7, IF(OR(STATUS7 = "red", STATUS8 = "red", STATUS9 = "red"), "red", IF(AND(STATUS7 = "green", STATUS8 = "green", OR(STATUS9 = "green", ISBLANK(STATUS9))), "green", IF((AND(OR(STATUS7 = "yellow", STATUS8 = "yellow", STATUS9 = "yellow"), STATUS8 <> "red", STATUS9 <> "red")), "yellow"))))