If AND or OR
Hi,
I have use below formula to show all parts of my process steps. It works well with red and green balls. But I would like to add if one of below columns is yellow (pending) the result in this cell should be yellow as well.
How can I modify the fomula:
=IF([Dev sample needed?]@row = "", "", IF(OR([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", "Red"))
Best Answer
-
I believe your'e getting an error because of the spaces between the IF statements and their open parentheses.
IF( versus IF (
Try this:
=IF([Dev sample needed?]@row = "", "", IF(AND([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", IF(OR([CAD upload on time ?]@row = "Yellow", [Pattern confirmed on time?]@row = "Yellow", [Tooling confirmed on time?]@row = "Yellow", [Materials confirmed on time?]@row = "Yellow"), "Yellow", "Red")))
Keep in mind that if you are looking into Status Symbol ball columns, you'll need to spell the text as "Yellow" with only one capital in order to display the ball, or recognize the ball colour. Otherwise it will look for the text, "YELLOW". Does that make sense?
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Christine,
First, your IF(OR statement means that if any of the column are green the you are reporting your process is Green - is this what you intend? If this is the case, it will be hard to get past this step in the logic as a "true" result will occur. Suggest you change this to "AND" so that ALL columns must be green before reporting the Process as "Green". If you do this, the try the following to report "Green", "Yellow" or "Red":
=IF (
[Dev sample needed?]@row = "",
"",
IF (
AND (
[CAD upload on time ?]@row = "Green",
[Pattern confirmed on time?]@row = "Green",
[Tooling confirmed on time?]@row = "Green",
[Materials confirmed on time?]@row = "Green"
),
"Green",
IF (
OR (
IF (
[CAD upload on time ?]@row = "Yellow",
"True",
"False"
),
IF (
[Pattern confirmed on time?]@row = "Yellow",
"True",
"False"
),
IF (
[Tooling confirmed on time?]@row = "Yellow",
"True",
"False"
),
IF (
[Materials confirmed on time?]@row = "Yellow"
"True",
"False"
),
),
"Yellow",
"Red"
)
)
)
-
Thanks for your help Rich Stowell ,
but it's still not working. I get an unparseable error. Any idea to fix it?
=IF ([Dev sample needed?]@row= "","", IF (AND ([CAD upload on time ?]@row= "GREEN", [Pattern confirmed on time?]@row = "GREEN", [Tooling confirmed on time?]@row= "GREEN", [Materials confirmed on time?]@row= "GREEN"), "GREEN", IF (OR (IF ([CAD upload on time ?]@row = "YELLOW", "TRUE" , "FALSE"), IF ([Pattern confirmed on time?]@row= "YELLOW", "TRUE","FALSE"), IF ([Tooling confirmed on time?]@row = "YELLOW", "TRUE", "FALSE"), IF ([Materials confirmed on time?]@row= "YELLOW", "TRUE","FALSE"),), "YELLOW", "RED")))
-
I believe your'e getting an error because of the spaces between the IF statements and their open parentheses.
IF( versus IF (
Try this:
=IF([Dev sample needed?]@row = "", "", IF(AND([CAD upload on time ?]@row = "Green", [Pattern confirmed on time?]@row = "Green", [Tooling confirmed on time?]@row = "Green", [Materials confirmed on time?]@row = "Green"), "Green", IF(OR([CAD upload on time ?]@row = "Yellow", [Pattern confirmed on time?]@row = "Yellow", [Tooling confirmed on time?]@row = "Yellow", [Materials confirmed on time?]@row = "Yellow"), "Yellow", "Red")))
Keep in mind that if you are looking into Status Symbol ball columns, you'll need to spell the text as "Yellow" with only one capital in order to display the ball, or recognize the ball colour. Otherwise it will look for the text, "YELLOW". Does that make sense?
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
-
Hi Christine, I'm glad I could help!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!