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

Question

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

Pete 26

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.

Accepted answer

1 additional answer

Your answer

Answer 1

Yi Lu_MSFT 17,611

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.

Pete 26 Reputation points

2022-07-04T08:55:56.603+00:00

Hi

That works thanks,

I actually need to make 2 fields mandatory based on the same validation rules, so the two below rules both work when used on their own but when I add them together I dont get the correct validation:

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

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

I need both [Account Number] AND [Date Investigation Completed] to be mandatory, something like the below:

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

How do we write it correctly?

Thanks again :)
Pete 26 Reputation points

2022-07-05T08:19:29.847+00:00

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

Update, the above works apart from it doesnt stop both [Account Number] & [Date Investigation Completed] from being blank, if both blank it allows it to save, where as it shouldnt as they both should be required?
Yi Lu_MSFT 17,611 Reputation points

2022-07-05T11:03:35.337+00:00

Hi @Pete
If you just need to stop both [Account Number] & [Date Investigation Completed] from being blank, there is a simple method: edit column > select "Require this column contains information"

As a result, if it is blank, when you edit the item it could not be saved:
Pete 26 Reputation points

2022-07-05T11:29:57.43+00:00

Hi

Need it so that neither [Account Number] or [Date Investigation Completed] can be blank when this is true:

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

Is this possible ?

Thanks

Yi Lu_MSFT 17,611

Yes, just use this 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)

I have also update this formula in my answer, you could check it there too.

mikhail jackson 0 Reputation points

2025-04-08T22:51:55.0066667+00:00

figured it out nvm

Answer 2

Pete 26

Awesome thanks !

Share via

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

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

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

1 additional answer

Your answer