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,

Attached are the screenshots.

Thanks!

Laís

Answer 2

Hi George,

Were you able to check the screenshots I sent you?

Thanks,

Laís

Answer 3

Hi Laís,

Thanks for posting back and sorry for my late reply.

Sorry, I didn't receive the notification email for your December 7th reply and just received your December 16th reply email. I apologize for making you wait so long!

Based on the screenshot of the Status column, I noticed that each option has a different icon in the Edit column pane. I think this may be causing the formula to not recognize the condition when [Status]="Deficiency". I would like to know if the icon is part of the option content or if it is added by the JSON code. To verify this, you can click on the option in the Edit Column pane and see if the icon is part of the option.

If the icon is not part of the option content, please point it out and let us know.

If the icon is part of the option content, I suggest you add it to the formula. You can try the three shorter formulas in my December 6 reply. Copy the entire option content from the Edit column pane, paste it into the formula to replace the "Deficiency" part, and then check again.

In addition, I suggest you create a new Choice column for the test. Please create a new Choice column, name it "Status1" and add the option "Deficiency". After that, you can try the following shorter formulas. See which formula can be saved.

The English formula:

=IF([Status1]="Deficiency",FALSE,TRUE)

The Portuguese formulas:

=SE([Status1]="Deficiency";FALSO;VERDADEIRO)

=SE([Status1]="Deficiency",FALSO,VERDADEIRO)

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

Sincerely,

George | Microsoft Community Moderator

Answer 4

Hi George,

Thanks for your reply! I am not sure how to check if the icon is part of the option content. I have added it through the column format option, see it attached.

Can you kindly guide me on how to add the symbol to the formula?

Thanks again!

Laís

Answer 5

Hi Laís,

Thanks for posting back.

Sorry, you don't need to check the icon. This was a mistake on my part. I assumed that the icon for the Choice option was added by some customized methods, which might affect the formula. However, I forgot that there is a new built-in formatting feature called “Choice pill”. Thank you for the screenshot that corrected my misunderstanding.

Based on the above, I was going in the wrong direction in troubleshooting the problem in the previous reply. Currently, I think the problem is caused by the incorrect function names in the formula, which in turn is affected by the default site language. In this case, I suggest you try the following steps to check the default site language.

Edit the site URL and access the Site languages page. Check the relevant information as shown below. If you cannot access the modified URL, could you please ask the site owner to access the Site languages page and check the default site language?

The original site URL:

https://<tenantname>.sharepoint.com/sites/<sitename>/SitePages/CollabHome.aspx

Modify the site URL (use "/_layouts/15/muisetng.aspx" to replace the part after <sitename>)

https://<tenantname>.sharepoint.com/sites/<sitename>/_layouts/15/muisetng.aspx

![](https://learn-attachment.microsoft.com/api/attachments/e4409eb4-842a-443c-bcea-5759749ab27b?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/078b1ee8-d2e4-47f8-b245-e92b26e7b6b1?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">

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

Sincerely,

George | Microsoft Community Moderator