Sharepoint list validation statement - multiple IF AND ORs to set a required field

Question

Hi

I have the below statement which makes the 'Account number' field required if 'Relationship stage' equals one of the two values:

=IF(OR([Relationship Stage]="Completed - AML",[Relationship Stage]="Completed - Fraud"),IF([Account Number]="" ,FALSE,TRUE),TRUE)

The above works OK but I need to check two fields for values to determine if the field is requried eg.

=IF(OR([Relationship Stage]="Completed - AML",[Relationship Stage]="Completed - Fraud"),AND(OR([Investigation Status]="Suspect",[Investigation Status]="Inconsistent",[Investigation Status]="Fraud"),IF([Account Number]="",TRUE,FALSE),TRUE))

So I am trying to check for a value match in both 'Relationship stage' AND 'Investigation status' to determine if the the 'Account number' should be required but whatever I try nothing seems to work, any ideas ?

(This is using Sharepoint list validation feature)

Thanks in advance.

Answer 1

Hi @Pete
You could use the following formula:

=IF(AND(OR([Relationship Stage]="Completed - AML",[Relationship Stage]="Completed - Fraud"),OR([Investigation Status]="Suspect",[Investigation Status]="Inconsistent",[Investigation Status]="Fraud")),IF(OR([Account Number]="",[Date Investigation Completed]=""),FALSE,TRUE),TRUE)  

As a result, the validation works( when [Relationship Stage]="Completed - AML", [Investigation Status]="Inconsistent", [Account Number]="", it fails) :

And the other cases are also right:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Awesome thanks !