Changing a cell based on text in two different cells, nested IF/AND/OR formula not working
Hello! I'm trying to change the text in a cell based on answers in two separate cells. I basically need approval from two separate people in order for a request to be approved and if one of them denies the request I need the status row to show "denied". The formula below works for this except in the case where the current provider request is pending(request sent) , but the requested provider has approved (it works the other way around, where requested provider is pending, but current provider has approved). Any help on what I'm missing?
=IF(AND([Current Provider Approval]@row = "Request Sent", [Requested Provider Approval]@row = "Request Sent"), "Pending", IF(OR([Current Provider Approval]@row = "Denied", [Requested Provider Approval]@row = "Denied"), "Denied", IF(AND([Current Provider Approval]@row = "Denied", [Requested Provider Approval]@row = "Denied"), "Denied", IF(OR([Current Provider]@row = "Request Sent", [Requested Provider Approval]@row = "Request Sent"), "Pending", IF(AND([Current Provider Approval]@row = "Approved", [Requested Provider Approval]@row = "Approved"), "Approved")))))
Best Answer
-
You are missing the word "Approval"
IF(OR([Current Provider]@row = "Request Sent", [Requested Provider Approval]@row = "Request Sent"
Answers
-
You are missing the word "Approval"
IF(OR([Current Provider]@row = "Request Sent", [Requested Provider Approval]@row = "Request Sent"
-
So basically you accidentally used the Column "Current Provider" instead of "Current Provider Approval" for one of your IF statements.
-
@Michael Culley thank you!! I had clicked on the wrong cell when building the formula! thank you for the extra set of eyes here :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!