IF / AND / OR WORKS ON EXCELL BUT NOT ON SMARTSHEET
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"))
Answers
-
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"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!