Changing a cell based on text in two different cells, nested IF/AND/OR formula not working

Options

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")))))

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!