Use Column Validation so that single text column requires an answer from a range of answers

[Imam1375]

Hi everyone,

Hope you're well. I have a SharePoint list, and in it, one of the fields is a single text field (that is used for a cascading dropdown on the form). Some users, instead of using the form, will copy and paste lines of data from Excel straight into this SharePoint list, via grid view. This is if they have lots of data to put in where its too time consuming to fill out the form each time...they'd only want to use Excel for this.

I need it so that the answer they put in for this particular single text field (called variance driver), has to be a specific answer from a specific range of answers. So for example, the 3 possible answers they should put in for 'variance driver' are machine costs, employee costs, tech costs. Users must put any one of these in, if they misspell it then the SharePoint list will flag it during grid view that they must enter them correctly.

Is there some sort of column validation I could use for this? Have been trying but no progress so far.

Appreciate your help.

[#2]

@Imam1375 Use column validation formula like: 

=OR([Variance Driver]="Machine Costs",[Variance Driver]="Employee Costs",[Variance Driver]="Tech Costs")

Column settings: 

Output: 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

[#3]

@ganeshsanap Hi Ganesh, thanks for your answer. Problem is, that it doesn't seem to work for me. The formula keeps returning #NAME? where variance driver is written. 

I have made sure that it is spelt exactly as it should be.

[#4 · Accepted solution]

@Imam1375 

1. You have to add formula to variance driver column
2. You have to use the correct display name of column enclosed within []
3. If not working using above suggestions, follow below classic experience approach:

Go to SharePoint list > Click on Settings (gear) icon > Select List settings > Scroll down to "Columns" section and click on variance column name.

Scroll down to Column Validation section and copy paste the formula in correct format like: 

Click "OK" button at the bottom of the page to save the changes to column settings.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

[#5]

Thank you, trying it via list settings worked

[#6]

Hi again. Just an update. The field that I wanted to use this for, has a cascading dropdown on the form (which is customized through power apps). When I put in the column validation formula, the choice selection in the cascading dropdown completely disappears, i.e the cascading dropdown shows no choices, it is completely blank. Do you know any solution to this? Thanks

[#7]

@Imam1375 If it is a choice field and choice options are predefined, is there any reason you need to use column validation for this field?

If you don't use column validation formula, is it showing choices properly? Column validation formula should not affect choice options. Check if your Power apps formulas are correct or not.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

[#8]

Actually it works fine, sorry I just made a mistake. The field I was looking at does not have any items for one of the cascading dropdown options, thats why it appeared blank.

The validation, in fact, does not affect cascading dropdowns, they both work fine.

[#9]

Another update/question. I have to implement this column validation across other lists too, with much longer ranges. When I tried to do one of them, it said that the formula has to be 1024 characters or fewer. Is there any way to work around this? Or is this an organizational thing?

[#10]

@Imam1375 Can you show your formula? Will check if there is any change to optimize it.

You cannot change this limit of characters in validation formula. This is defined limitation by Microsoft. If column validation is not working, you can check for list validation options from list settings (not sure about the limit of characters here).

Also, I don't know why you need formula if you are using choice column with predefined choices/options. As you have predefined choices, user has to select either of them using drop down.

---

Please consider giving a Like if my post helped you in any way.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs