0

I am a newbee and hope you can help me as I've read many other posts and still stuck.

I have multiple conditions that are unrelated and attempting to add the proper formula to the validation settings of my SP list.

This is the formula I have working so far for 2 conditions:

=IF([CRAIDL Type]="Risk",IF(Priority<>"",IF(Status="Closed",IF([Closed Date]<>"",TRUE,FALSE),FALSE),FALSE),FALSE)

How can I nest these other conditions from this formula?
-Require [Priority] for other CRAIDL types in addition to risk: Action, Issue, and Discovery.

  • If [Status] is "open", "in progress", or "ready to test", the [next review date] should NOT be blank & [CLOSED DATE] should be blank.

One user message for all is ok; I'm trying to avoid Power Apps. TIA!

Improve this question
3
  • Could you define your validation in a simple "AND" and "OR" conditions? You can take a look at this formula reference to see how to construct formula using AND/OR
    – Matiur Rahman
    Dec 15, 2020 at 23:03
  • Thank you for commenting. I reviewed that link before posting and was unable to solve but could be due to my newness. If CRAIDL Type= ___(there's 4 of them), the Priority field must be populated (not blank).Also, if Status selection is OPEN, In Progress, or Ready to test, one field (next review date) should NOT be blank and another (closed date) should be blank. I'm trying to figure out how all of this gets nested with the proper syntax.
    – Jamie
    Dec 16, 2020 at 13:02
  • Sure.. no worries. Will post my response little later.
    – Matiur Rahman
    Dec 16, 2020 at 14:40

2 Answers 2

Reset to default
0

You could use AND function to nest mulitple conditions.

Reference: https://support.microsoft.com/en-us/office/and-function-9ca21054-a466-477e-98bf-fc79e62afb3c

Syntax

AND(logical1,logical2,…)

Logical1,Logical2,… are 1 to 30 conditions you want to test that can be either TRUE or FALSE.

Improve this answer
1
  • Thanks, I did review that and try to apply it but continue to get syntax errors as I'm not building the formula correctly, so hoping you professionals know how to nest these rules together properly.
    – Jamie
    Dec 16, 2020 at 13:05
0

Based on my understanding of your requirements, here is the formula that you can try. Please note that you need to update the column names and choice column values to match your list.

=IF(NOT(ISBLANK(CRAIDLType)),IF(ISBLANK(Priority),FALSE,IF(OR(OR(Status="Open",Status="In Progress"),Status="Ready to Test"),IF(AND(NOT(ISBLANK(NextReviewDate)),ISBLANK(ClosedDate)),TRUE,FALSE),TRUE)),TRUE)

enter image description here

enter image description here

Valid Entry

enter image description here

Updated formula based on the updated requirements:

Pattern to use: =IF( OR( NOT(Formula1), NOT(Formula2), NOT(Formula3), NOT(Formula4) ),FALSE,TRUE)

Updated formula:

=IF( OR( NOT(IF(OR([CRAIDL Type] ="Risk",[CRAIDL Type] ="Action",[CRAIDL Type] ="Discovery",[CRAIDL Type] ="Issue"),IF([Priority]<>"",TRUE,FALSE),TRUE)), NOT(IF(Status="Closed",IF([Closed Date]<>"",TRUE,FALSE),TRUE)), NOT(IF(OR([Status] ="Open",[CRAIDL Type] ="In Progress",[CRAIDL Type] ="Ready for Retest ISSUE”),IF([Next Review Date]<>"",TRUE,FALSE),TRUE)), NOT(IF(OR([Status] ="Open",[Status] ="In Progress",[Status] ="Ready for Retest ISSUE”),IF([Closed Date]="",TRUE,FALSE),TRUE)) ),FALSE,TRUE)
Improve this answer
9
  • I appreciate your effort, I updated my column names accordingly using your formula but resulted in a syntax error.
    – Jamie
    Dec 17, 2020 at 21:58
  • Can you post the formula with the updated column names? I will see if there is any syntax error after you have updated the column names. Also, make sure the column names are correct.
    – Matiur Rahman
    Dec 17, 2020 at 22:39
  • Thanks.. I've gotten these 4 formulas to work successfully on their own. How do I combine them? In other words, all 4 validations should be applied, if any of these 4 are false, return the one error. I'll post each formula in a separate comment as characters are limited.
    – Jamie
    Dec 18, 2020 at 1:41
  • =IF(OR([CRAIDL Type] ="Risk",[CRAIDL Type] ="Action",[CRAIDL Type] ="Discovery",[CRAIDL Type] ="Issue"),IF([Priority]<>"",TRUE,FALSE),TRUE)
    – Jamie
    Dec 18, 2020 at 1:44
  • =IF(Status="Closed",IF([Closed Date]<>"",TRUE,FALSE),TRUE)
    – Jamie
    Dec 18, 2020 at 1:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.