1

I am trying to create a column validation based on the other column's output and I constantly get the error.

I have 2 fields:

  • Testing - choice column with the value "Yes" or null.
  • Test date - date column

I want to make column "Test date" mandatory if the user selects "Yes", otherwise it should not be required.

My current formula is the following:

=IF([Testing] = "Yes"; IF(ISBLANK([Test date]); FALSE; TRUE); TRUE)

Based on the language selection I am using semicolon instead of comma in all of calculated fields.

Could anyone suggest how should I modify my validation formula?

1 Answer 1

1

Your formula seems correct to me. I think you are trying to reference [Testing] column under Column Validation settings for [Test date] column.

But, you cannot add reference to other columns under Column Validation settings in SharePoint.

So, you have to add your formula under List Validation settings.

Follow below steps:

  1. Go to SharePoint list
  2. Click on Settings icon and select List settings
  3. Select Validation settings
  4. Add formula & user message and click Save

Example screenshot:

enter image description here

Similar Threads:

  1. Validation Column Not Working
  2. List validation if then syntax
6
  • Thank you so much for your answer, the formula worked! However, there is an error related to it that I cannot solve. When I select "Yes", I cannot save the form with a blank "Test date" field. I get the correct error message too, however, some other unrelated column is highlighted instead as a required field. So although it works technically, but visually it looks confusing. I have 2 similar lists and I have the same error in both. Any suggestion why might it be?
    – ekoz
    Commented Dec 7, 2021 at 14:10
  • 1
    Do you have column validation settings applied on that column? When you apply list validation settings, error message will be shown at the top of list form & not near "Test date" field. Commented Dec 7, 2021 at 15:01
  • I have one last question. I want to add one more validation for the column "Test result" and my current formula is: =IF([Testing]="Yes";IF(OR(ISBLANK([Test date]);ISBLANK([Test result)]));FALSE;TRUE);TRUE) Both columns "Test date" and "Test result" should be required if the value in column "Testing" is "Yes". Currently, if either "Test date" or "Testing" is left blank, the column "Test date" is marked in red as mandatory and I get the error message. How can I change it so that only the blank column is marked as required, not only the first value in my formula?
    – ekoz
    Commented Dec 8, 2021 at 7:41
  • Formula you given in above comment has syntax errors. Use this: =IF([Testing]="Yes";IF(OR(ISBLANK([Test date]);ISBLANK([Test result]));FALSE;TRUE);TRUE). Again, when you apply list validation settings, error message should be shown at the top of list form & not near "Test date" field. Can you add screenshot (to your original question) of where & how the error message is shown? Commented Dec 8, 2021 at 7:52
  • 1
    Might be some issue with recent SharePoint updates. Try raising a support ticket with Microsoft for error message issue, check Get Online support. Commented Dec 8, 2021 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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