How to validate a column in Sharepoint to it can be mandatory when another column is filled with a specific information?

Question

I am not being able to validate a column in sharepoint. I have a column named "Status" and once the option "deficiency" is selected at that column, I want the column "Deficiency Deadline" to be mandatory. Whit that, I added the following formula to the validation field of the "Deficiency Deadline" column, however, not working: =IF([Status]="Deficiency", NOT(ISBLANK([DeficiencyDeadline])), TRUE). Does anyone know how to write this formula so it can work?

Answer 1

Hi George,

I have checked and it says that the “The default language of this site is English. Enable translation in multiple languages to allow the translation of the site name, navigation, footer, pages, and news.”

However, I did try the formulas in English as well and they were also not working :(

Answer 2

Hi Laís,

Thanks for posting back and sharing the results.

This is not normal. I think the formula is correct, however, there may be some very easily overlooked issues, such as characters, that are causing SharePoint to fail to save the formula. In this case, I suggest you try the following steps to input the formula and then check again..

1. Run Notepad and create a new txt file.
2. Edit the Status column (down arrow> Column settings> Edit), click on the "Deficiency" option in the Edit column pane. The value "Deficiency" should be selected. Press Ctrl+ C to copy, then paste the value in the txt file.
3. Go to the Validation Settings page and click the Formula box. Make sure you can see the cursor.
4. Make sure the current keyboard is in English and not in another language.
5. Manually input the formula, add the column from the "Insert Column" box (select the column and click "Add to formula"), and copy/paste the value "Deficiency" from the txt file into the Formula box, as shown below.

You can refer to this formula to manually enter parts other than Column names and the value "Deficiency". Note there are two parentheses after the Deficiency Deadline. See if the error message still appears.

=IF(Status="Deficiency",NOT(ISBLANK([Deficiency Deadline])),TRUE)

We look forward to your response. Thanks for your cooperation. 😊

Sincerely,

George | Microsoft Community Moderator

Answer 3

Hi Laís,

If the error message still appears when saving, I suggest you use a different browser to test.

In addition, I recommend you create a new Status column and a new Deficiency Deadline column for test.

For example, you can create a new Choice column, name it "Status 1" and add an option "Deficiency", create a new Date column, name it "Deficiency Deadline 1", and then try the following formula on your end.

=IF([Status 1]="Deficiency",NOT(ISBLANK([Deficiency Deadline 1])),TRUE)

We look forward to your response. Thanks for your cooperation. 😊

Sincerely,

George | Microsoft Community Moderator