Please assist with this formula. Thanks.
![AnnS](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
All data is in a single sheet.
I need all rows in a column titled "Status" to be populated with "APPROVED" when the following conditions are met: "Yes" in column titled "BU Approval", "Green" or "Yellow" in column titled "B", "Accepted" or "N/A" in column titled "C", date in column titled "D" is today or in future, Box in column titled "E" is checked.
Here's the formula in column "Status" with "unparseable" error
=IFERROR ((AND [A] @row="Yes", [B] @row="Green" "Yellow", [C] @row="Accepted" "N/A", [D] @row>=TODAY (), [E] @row=1, "APPROVED"), "")
Best Answer
-
Hi @AnnS,
You need some OR statements in there for the multiple options:
=IF(AND([BU Approval]@row = "Yes", OR(B@row = "Green", B@row = "Yellow"), OR(C@row = "Accepted", C@row = "N/A"), D@row >= TODAY(), E@row = 1), "APPROVED", "")
Example output:
Hope this helps - if there any issues just post and we can work them out!
Answers
-
@AnnS it looks like you have a space between the column and the @row. It should be for example [A]@row.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
thanks - i removed the space before @row as suggested - still getting unparseable error
-
I believe this covers all of the criteria.
=IFERROR(IF(AND([BU Approval]@row = "Yes", OR([B]@row = "Green", [B]@row = "Yellow"), OR([C]@row = "Accepted", [C]@row = "N/A"), [D]@row <= TODAY(), [E]@row = 1), "APPROVED", ""),"")
-
Hi @AnnS,
You need some OR statements in there for the multiple options:
=IF(AND([BU Approval]@row = "Yes", OR(B@row = "Green", B@row = "Yellow"), OR(C@row = "Accepted", C@row = "N/A"), D@row >= TODAY(), E@row = 1), "APPROVED", "")
Example output:
Hope this helps - if there any issues just post and we can work them out!
-
Formula from Nick worked. Thanks to all who responded!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!