I'm having issues with validation on SP2010. I've created a list with 8 possible shifts my employees can choose and want them to select their most to least preferred shift. I've also set it to they cant see each other's picks. I want to make sure there's no duplicates.

It's my first time using formulas and have been looking online for a better way to do this but haven't found one. To do this, I came up with this formula:

=IF(OR([0700-1500]<>[0800-1600]), if(and([0700-1500]<>[0900-1700]),if(and([0700-1500]<>[1000-1800]), if(and([0700-1500]<>[1100-1900]), if(and([0700-1500]<>[1330-2100]), if(and([0700-1500]<>[1430-2200]), if(and([0700-1500]<>[1530-2300]), if(and([0800-1600]<>[0900-1700]),if(and([0800-1600]<>[1000-1800]), if(and([0800-1600]<>[1100-1900]), if(and([0800-1600]<>[1330-2100]), if(and([0800-1600]<>[1430-2200]), if(and([0800-1600]<>[1530-2300]), if(and([0900-1700]<>[1000-1800]), if(and([0900-1700]<>[1100-1900]), if(and([0900-1700]<>[1330-2100]), if(and([0900-1700]<>[1430-2200]), if(and([0900-1700]<>[1530-2300]), if(and([1000-1800]<>[1100-1900]), if(and([1000-1800]<>[1330-2100]), if(and([1000-1800]<>[1430-2200]), if(and([1000-1800]<>[1530-2300]), if(and([1100-1900]<>[1330-2100]), if(and([1100-1900]<>[1430-2200]), if(and([1100-1900]<>[1530-2300]), if(and([1330-2100]<>[1430-2200]), if(and([1330-2100]<>[1530-2300]), if(and([1430-2200]<>[1530-2300]),true,false))

Unfortunately, the formula is too long. is there a way to shorten it?

Also, just to mention, Sharepoint designer has been disabled and cant use jquery.

Any help would be great! Thanks, Jeff

  • its impossible to read the formula, be aware you can only nest 7 levels deep in Formulas. Isn't it easier to reverse your logic and let users select timeslots from Columns: Shift1 , Shift2 , ... Jul 19, 2015 at 16:27

1 Answer 1


To shorten formulas, you need to create calculated columns that represent parts of the formula. After that, you create a formula based on calculated fields. I hope you find it useful.

  • cool thanks for the info. Can you tell me what should be in my list validation? I tried =Formula1+[Formula 2] but that didn't work
    – Jeff
    Jul 19, 2015 at 18:12
  • Nevermind I got it! You've been a great help!
    – Jeff
    Jul 19, 2015 at 18:38
  • I'm glad to read it! Jul 20, 2015 at 10:11

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.