1

I need to add a column validation to my list.

The requirement for the validation is that the column [Body] cant be empty if the column [Status] is one of this statuses: "1-Cancelled" or "2-Awaiting Information from Requester".

I tested the below formula in Excel which is working, but not working in the SharePoint. 

=NOT(
AND(
OR(
[Status]="1-Cancelled",[Status]="2-Awaiting Information from Requester"), ISBLANK([Body])
))

Please help me to find the right formula. Thanks in advance

Improve this question
2
  • I tried the following formula, but it is not working: =IF(AND(ISBLANK([Body])=TRUE,[Status]="1-Cancelled"),FALSE,TRUE)
    – juanlo
    Commented May 23, 2020 at 7:14
  • What is the data type of [Body] column?
    – Ganesh Sanap - MVP
    Commented May 23, 2020 at 7:24

1 Answer 1

Reset to default
0

Try below formula:

=IF(OR([Status]="1-Cancelled",[Status]="2-Awaiting Information from Requester"),IF(ISBLANK([Body]), false, true), true)

Note:

  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

  2. Also note that it is not possible to achieve using validation formulas if [Body] is Multiple lines of text field.

Improve this answer
7
  • Thanks for your reply Ganesh. I got an error: "The formula contains a syntax error or is not supported."
    – juanlo
    Commented May 23, 2020 at 7:40
  • Oops... Try updated formula in my answer. Also you need to add this formula in List Settings-->Validation settings.
    – Ganesh Sanap - MVP
    Commented May 23, 2020 at 7:45
  • Thanks Ganesh. I also tried replacing all the comma with semicolon, but I got the same error, I have other validations with commas. I tried on the "validation settings" on the List settings, but I got a different error: "One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas." [Status] is a choice and [Body] is Multiple lines of text
    – juanlo
    Commented May 23, 2020 at 7:57
  • Yes, if body is multiple lines of text then it is not possible using validation settings to achieve what you want because formulas don't support multiple lines of text field.
    – Ganesh Sanap - MVP
    Commented May 23, 2020 at 8:05
  • 1
    I found a workaround to solve this problem. Change the column [Body] to single text, apply the validation and then change the type again to multiple text. I will upvote, your answer helped me
    – juanlo
    Commented May 23, 2020 at 11:05

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.