How to add new criteria to the current formula

HZAR

Hi

I have the formula below which is triggering the Manager Approval to Yes if threshold if meet. Right now i need to add another depended which is buyer status which is if cancel then the manager status will be Not Required. Need help on this

Screenshot 2024-05-07 143724.png

Screenshot 2024-05-07 143626.png

Current formula

=IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO"))

Bassam Khalil

Hi @HZAR

Please try the following formula:

=IF([Buyer Status]@row = "cancel", "Not Required", IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO")))

This formula works as follows:

1. It first checks if the Buyer Status for the current row is "cancel." If it is, the formula immediately returns "Not Required."
2. If the Buyer Status is not "cancel," it then checks if the [xx Proposed Settlement ($USD)] value meets or exceeds the {Buyer Supervisor Limit}. If it does, it returns "YES."
3. If the [xx Proposed Settlement ($USD)] value does not meet the threshold, it checks whether this value is empty (""). If it's empty, it returns "TBD."
4. If none of the above conditions are met, it returns "NO."

Make sure to replace [Buyer Status] with the actual column name in your Smartsheet where the buyer status is recorded. This formula assumes that the buyer status that triggers "Not Required" is specifically "cancel" and is case-sensitive. If there are variations in how "cancel" is recorded (like "Cancelled" or "canceled"), you'll need to adjust the condition to account for these variations.

HZAR

Hi Mr Bassam Khalil

Thanks for the input. Really appreciate it 😍

Bassam Khalil

You are welcome @HZAR

HZAR

Hi Mr Bassam

Is it possible for me to add 1 more criterial for example like below. If yes is then how can I add the criteria? Hope that you can guide me. Thanks

=IF([Buyer Status]@row = "cancel", "Not Required", IF ([Buyer]@ row="CC2gmail,"cw@gmail","NO",IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO")))

dojones

Yes,

IF([Buyer Status]@row = "cancel", "Not Required", IF ([Buyer]@ row="CC2gmail,"cw@gmail","NO",IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO"

You could add it at any point in the formula, but I'll describe it at the end of formula. Go to this point

 IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO")))
  
Add another IF statement at end after "TBD" and before "NO".  Add an extra parenthesis after "NO".

IF([xx Proposed Settlement ($USD)]@row = "", "TBD", IF([xxx]@row = [yyy]@row,"Value to display if true", "NO"))))

HZAR

What if I want to change

If the status is cancel -No

If CC and CW for all status-No

Can you help to advise since I have the formula below. Thanks

=IF([GSP Status]@row = "Cancel", "NO", IF([MSI Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([MSI Proposed Settlement ($USD)]@row = "", "TBD", IF(GSP@row = "cc@gmail", "cw@gmail", "NO", "NO"))))

HZAR