I want my data entry to not be on a list of exempted entries. I want to use a list of entries. But am open to suggestion Tested in excel by placing exceptions in a column. Extracted to sharepoint a small table to create a library of entries

error message I enter reads "This Job Code is exempt from this process"

in sharepoint validation i enter this formula... IF([Job Code]=[Exceptions],"true","False") which isnt what i want however;

[exceptions] after i save the formula converts to this..

IF([Job Code]=Exceptions,"true","False")

this didnt work correctly so i reversed the outcomes IF([Job Code]=[exceptions],"False","True") which is the result i want, if the number exists in the exceptions collumn i dont want them to be able to save!

In excel i used this formula...


this does exactly what i want...but i want this to happen in sharepoint and not validate the entry...

excel snip excel snip2

i have tried other formulas with no luck, i keep getting a syntax error when saving... examples are...

=IF(SUMPRODUCT(--(A2:F2=A3)),TRUE,FALSE) this works but i dont know how to convert the single cell and range/array into sharepoint Any help is greatly appreciated!

2 Answers 2


Unfortunately, the Validation Setting formula is only applied to specific fields on the same ROW.

Check The Supported and Unsupported Columns In SharePoint Calculated Column Formula

Therefore, you can't search for column values and validate against these values in the SharePoint validation setting or calculated field like Excel formula!


We need using some code to achieve it. The following code for your reference, add the code into script editor web part into new/edit form page.

<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
function PreSaveAction() { 
    var jobCode = $("input[title='Job Code']").val();

        $("input[title='Job Code']").after('<br/><span class="ms-formvalidation ms-csrformvalidation" id="Error_JobCode"><span role="alert">This Job Code is exempt from this process.<br></span></span>'); 
        return false;
    return true;
function isJobCodeExempt(jobCode){
    var flag=false;
    var listID=_spPageContextInfo.pageListId.replace("{","").replace("}","");
        url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists(guid'"+listID+"')/items?$filter=Exceptions eq "+jobCode,
        type: "GET",
        headers: {
            "Accept": "application/json;odata=verbose",
        success: function (data) {

        error: function (data) {
    return flag;

enter image description here

  • I was able to achieve this by putting conditions and rules on my submit button. rule 1 query using a data connection, rule 2 2 conditions JobCodeEntry is not blank, and jbocode entry does not match column 2 = specific output.... rule 3 same as 2 but entry does match, performs different output solved!
    – Neigh
    Nov 1, 2018 at 14:56

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.