2

I am trying to nest 6 IF(OR) statements in SP 2007 for a calculated field in a list - But it seems as if it is getting too long, and I get the syntax error thrown at me. I tested half of the length and that went fine,the other half as well, however it seems to go wrong at some point after the appending of 4th IF.. I would really appreciate any help to make this work! :)

=IF(OR([Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project    ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project  ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x"),"HEY","")&IF(OR([Project  ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x"),"YO","")&IF(OR([Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x"),"HI","")&IF(OR([Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x"),"HELLO","")&IF(OR([Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x"),"MJALLO","")&IF(OR([Project ID]="x",[Project ID]="x",[Project ID]="x",[Project ID]="x"),"BYE","")

(project IDs are x'ed out for simplicity - they are all different IRL)

  • Have you tried it in Excel? – XristosK Sep 6 '14 at 22:35
  • NOPE-Mostly because I dont need it working in excell :) – SPDev Sep 10 '14 at 11:18
  • formulas for calculated fields are based on Microsoft Excel functions and syntax : msdn.microsoft.com/en-us/library/office/… – XristosK Sep 10 '14 at 14:36
  • I know this.. but since both halves work seperately, the syntax is clearly correct so why test in excell? I am looking for an answer to how to make SP 2007 accept more than 3 concatenated IF's – SPDev Sep 11 '14 at 7:13
1

Ok, I deleted the previous answer. Now I'm back in the office in a SP environment where I can test. This worked for me:

=IF(OR([Project ID]="A1",[Project ID]="A1",[Project ID]="A1",[Project ID]="A1",[Project ID]="A2",[Project ID]="A2",[Project ID]="A2",[Project ID]="A2",[Project ID]="A3",[Project ID]="A3",[Project ID]="A3",[Project ID]="A3",[Project ID]="A3"),"HEY","")&IF(OR([Project ID]="B1",[Project ID]="B1",[Project ID]="B1",[Project ID]="B2",[Project ID]="B2",[Project ID]="B3",[Project ID]="B3"),"YO","")&IF(OR([Project ID]="C1",[Project ID]="C1",[Project ID]="C1",[Project ID]="C2",[Project ID]="C2",[Project ID]="C2",[Project ID]="C2",[Project ID]="C3",[Project ID]="C3",[Project ID]="C3",[Project ID]="C3",[Project ID]="C4",[Project ID]="C4",[Project ID]="C4",[Project ID]="C4",[Project ID]="C5",[Project ID]="C5",[Project ID]="C5",[Project ID]="C5",[Project ID]="C6",[Project ID]="C6"),"HI","")&IF(OR([Project ID]="D1",[Project ID]="D1",[Project ID]="D1",[Project ID]="D2",[Project ID]="D2",[Project ID]="",[Project ID]="D2",[Project ID]="D3",[Project ID]="D3",[Project ID]="D3"),"HELLO","")&IF(OR([Project ID]="E1",[Project ID]="E1",[Project ID]="E1",[Project ID]="E2",[Project ID]="E2",[Project ID]="E2",[Project ID]="E2"),"MJALLO","")&IF(OR([Project ID]="F1",[Project ID]="F1",[Project ID]="F2",[Project ID]="F3"),"BYE","")

I change dthe "x" with different values. I hope this helps.

enter image description here

  • Looks alot like the stuff I already have.. ? except all my project IDs are different from each other..is there a syntax difference that I missed? :) – SPDev Sep 10 '14 at 11:19
  • sorry for the late reply - I've not been able to comment earlier – SPDev Sep 10 '14 at 11:26
  • Initially, the only issue I found in your code was spacing. If you notice I edited it when I saw those extra spaces. But the rest remained pretty much the same. – cubanGuy Sep 11 '14 at 2:01
  • are you testing in SP 2007? – SPDev Sep 11 '14 at 7:11
  • Actually 2010, but it should be the same. Also I tested on MS Excel. – cubanGuy Sep 12 '14 at 12:01
0

there is a limitation for no of if statements for SharePoint calculated field. refer this article : http://office.microsoft.com/en-us/windows-sharepoint-services-help/introduction-to-data-calculations-HA010121588.aspx

and best alternative to overcome your issue will be use choose statements or to use multiple calculated columns.and keep in mind that there is a limitation of 102 characters also.

  • if this was helpful or if it's the answer you expect let me know – 007ruwan Sep 26 '14 at 5:07

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.