1

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 , ... – Danny '365CSI' Engelman Jul 19 '15 at 16:27
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 '15 at 18:12
  • Nevermind I got it! You've been a great help! – Jeff Jul 19 '15 at 18:38
  • I'm glad to read it! – Juan Pablo Pussacq Laborde Jul 20 '15 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.