4

I have to create a list wherein when user inputs a data to a column the corresponding calculated column should take the data and analyze the condition and then return the value, but my SharePoint online is giving me error when I try to add more than 12 nested if conditions, any workaround please suggest?

Update : This is my formula :

=IF([pn]="S.Vish,ab","xyz@abc.com",IF([pn]="S.Ash,","xxx@abc.com",IF([pn]="S.Kav,ab","kav@abc.com",IF([pn]="S.Sun,ab","sun@abc.com",IF([pn]="S.Urb,ab","urb@abc.com",IF([pn]="S.John,ab","john@abc.com",IF([pn]="S.Paw,ab","paw@abc.com",IF([pn]="S.Ram,","thy@abc.com",IF([pn]="S.Din","din@abc.com",IF([pn]="S.Moh,","sin@abc.com",IF([pn]="S.Man","man@abc.com",IF([pn]="S.Vik,","vik@abc.com",IF([pn]="S.Vij",,"vij@abc.com",IF([pn]="S.Pram,ab","pat@abc.com",IF([pn]="S.Ank,ab","ank@abc.com",IF([pn]="S.Sai,ab","sai@abc.com",IF([pn]="S.Ano,ab","ano@abc.com",IF([pn]="S.Ars,ab","ars@abc.com",IF([pn]="S.She,ab","she@abc.com",IF([pn]="S.Gov,","tgr@abc.com",IF([pn]="S.Nal","nal@abc.com",IF([pn]="S.San,","san@abc.com",IF([pn]="S.Sun","sun14@abc.com","Not OK"))

3

A late answer for clarification:

  • Maximum nest IFs: SharePoint 2007 and 2010 only allow 7. SharePoint 2013 and later (including Online) allows 19. Larger values are possible by using batches of 7 or 19 or less and concatenating or adding the results.
  • Maximum formula length: SharePoint 2007 and 2010 only allow 1024 characters. (2013 and later is around one billion!)

The following is works in 2013 and later by using batches of 19 or less. It won't work in 2007 and 2010, even in batches of 7, due to the total length.

if(State="AL","Alabama",
if(State="AK","Alaska",
if(State="AZ","Arizona",
if(State="AR","Arkansas",
if(State="CA","California",
if(State="CO","Colorado",
if(State="CT","Connecticut",
if(State="DE","Delaware",
if(State="FL","Florida",
if(State="GA","Georgia",
if(State="HI","Hawaii",
if(State="ID","Idaho",
if(State="IL","Illinois",
if(State="IN","Indiana",
if(State="IA","Iowa",
if(State="KS","Kansas",
if(State="KY","Kentucky",
if(State="LA","Louisiana",
if(State="ME","Maine",""))))))))))))))))))) &
if(State="MD","Maryland",
if(State="MA","Massachusetts",
if(State="MI","Michigan",
if(State="MN","Minnesota",
if(State="MS","Mississippi",
if(State="MO","Missouri",
if(State="MT","Montana",
if(State="NE","Nebraska",
if(State="NV","Nevada",
if(State="NH","New Hampshire",
if(State="NJ","New Jersey",
if(State="NM","New Mexico",
if(State="NY","New York",
if(State="NC","North Carolina",
if(State="ND","North Dakota",
if(State="OH","Ohio",
if(State="OK","Oklahoma",
if(State="OR","Oregon",
if(State="PA","Pennsylvania",""))))))))))))))))))) &
if(State="RI","Rhode Island",
if(State="SC","South Carolina",
if(State="SD","South Dakota",
if(State="TN","Tennessee",
if(State="TX","Texas",
if(State="UT","Utah",
if(State="VT","Vermont",
if(State="VA","Virginia",
if(State="WA","Washington",
if(State="WV","West Virginia",
if(State="WI","Wisconsin","")))))))))))
2

Break up your one Calculated Column into multiple Calculated Columns, then reference the (sub) Calculated Columns in your final result (Calculated Column)

  • can you please guide me how to do that ? – Vish Dec 16 '17 at 5:06
1

As a workaround, we can create a single text column to instead of the calculated column, then use SharePoint designer workflow to set the value to the column. SharePoint Designer provide a Graphic User Interface to edit the logic of a workflow, it will be more comfortable to work in a SharePoint Designer workflow if you may have complex logic.

The links below about SharePoint Designer would be helpful:

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/workflow-actions-quick-reference-sharepoint-workflow-platform

https://docs.microsoft.com/en-us/sharepoint/dev/general-development/creating-a-workflow-by-using-sharepoint-designer-and-the-sharepoint-wo

  • i tried workflow,but the fact is that ,it seems to be running slow as compared MS-FLOW ,i have return this value to MS-FLOW ,so flow is not waiting for the workflow to run and get the value and the go to next step,instead even before workflow completes it goes next step and then flow falils – Vish Dec 15 '17 at 5:07
  • If workflow not meet you, we can also use some JavaScript to set value in New/Edit form page. – LZ_MSFT Dec 15 '17 at 6:25
  • can u please guide me how to do that ,i am new to this :) – Vish Dec 16 '17 at 5:09
1

I don't think having more than 12 nested IFs is the problem. Are you grouping your IFs in groups of 7? I have a test list where I added 13 IFs statements and it works.

List with 13 options

Nested IFs

  • no i was trying it as a single batch,thanks for this i will try to separate like this and will let you know :) – Vish Dec 16 '17 at 9:46
  • I tried this also ,but even if i do as a batch of 7,it is not allowing me to add more than 12 :( – Vish Dec 16 '17 at 12:35
  • In this formula you have 7 nested IFs (from Jan till Jul) That is the maximum SharePoint Formulas can handle. @Vish, post your Formula in your question so we can have a look – Danny '365CSI' Engelman Dec 17 '17 at 11:50
  • Updated please check – Vish Dec 18 '17 at 8:53

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.