0

I have two columns in an excel spreadsheet. One is Entity and one is Country. I have created the Country column in SharePoint as a calculated column and pasted the list of entities into the Entity column as a choice.

For the calculated column, I want the country to be automatically populated according to the entity that has been selected from the drop down.

The calculated column works to an extent, but has started giving me the following issues:

This is a short extract from the entire formula which includes 20 IF statements and then an &IF statement to add another 20 countries.

=IF(OR(Entity="Entity1"),"Peru",IF(OR(Entity="Entity2)",Entity="Entity3",Entity="Entity4",Entity="Entity5",),"Panama",IF(OR(Entity="Entity6"),"Serbia",IF(OR(Entity="Entity7",),"South Africa",IF(OR(Entity="Entity8",Entity="Entity9"),"Spain",IF(OR(Entity="Entity10",Entity="Entity11",Entity="Entity12",Entity="Entity13"),"Sweden")))))

Issue 1: The formula is accepted, but when looking at the list the column shows PeruFALSE, PanamaFALSE, SerbiaFALSE, etc.

How can I remove this FALSE from the column name?

Issue2: I have two more countries to add (Zambia, with 9 entities and Switzerland with 2 entities). I have tried adding these to the string after the & as well as creating another &IF statement and adding them there, but SharePoint keeps telling me I have a syntax error, even though I am using exactly the same format as the previous code that worked.

0

I guess the calculated column has some limit for If statements and as per my knowledge its 7 if statements.

As you said you have manage to includes 20 IF statements and then an & IF statement to add another 20 countries. And I am assuming that because of this you are getting result as PeruFALSE, because first set of 20 statements returning Country name and Second set of 20 statements returning false. So its concatenating strings and giving result as PeruFALSE. (This is my assumption).

A better approach will be create workflow which will handle this complex logic and return an expected result.

0

The formula you posted above is riddled with wrongly placed braces and quotes. No wonder it does not work.

=IF(OR(Entity="Entity1"),"Peru"[...]

Why use OR() with only one condition?

[...]IF(OR(Entity="Entity2)",Entity="Entity3",Entity="Entity4",Entity="Entity5",),

There is a closing bracket after the first condition, but it is INSIDE the quotes ...

There is a comma after the last condition, but no further parameter.

Etc.

In short, the whole formula is a mess. No wonder SharePoint is complaining about syntax errors. Check opening and closing quotes. Check opening and closing braces.

As a general troubleshooting guide for calculated columns in Sharepoint: Create a table in Microsoft Excel with the same column names, then create/verify the formula in Excel. If it works in Excel, it will work in SharePoint.

But with such a complex formula there are other options.

Consider using a lookup column into a list that has all the entities.

If it's not that simple, you could use a workflow to populate the field by looking up the Entity and adhering to additional rules.

0

You can use any text editor to format your code with line-breaks and indents, then paste it into a Formula. SharePoint will strip them on saving

I did not change anything in your Formula, only added line-breaks and spaces

As Teylyn said.. some syntax errors..

=IF(OR(Entity="Entity1"  <-- one parameter, not an error, but OR is not needed
      )
    ,"Peru"
    ,IF(OR(Entity="Entity2)" <-- ) in string, so Entity2 will never be matched
          ,Entity="Entity3"
          ,Entity="Entity4"
          ,Entity="Entity5"
          ,            <-- extra parameter separator
          )
       ,"Panama"
       ,IF(OR(Entity="Entity6"
             )
           ,"Serbia"
           ,IF(OR(Entity="Entity7"
                  ,            <-- extra parameter separator
                 )
               ,"South Africa"
               ,IF(OR(Entity="Entity8"
                     ,Entity="Entity9"
                     )
                   ,"Spain"
                   ,IF(OR(Entity="Entity10"
                         ,Entity="Entity11"
                         ,Entity="Entity12"
                         ,Entity="Entity13"
                         )
                       ,"Sweden"
                            <-- no False branch, Not an error but will return FALSE
                      )
                  )
              )
          )
      )
         <-- Missing ) closing IF 

You can take a different approach to circumentvent the limitation of 7 nested IFs

=REPLACE(Entity,1,LEN("Entity"),"")

Gets you the entity number

The Choose function takes 30 parameters (one index + 29 )

That is the equivant of a Casestatement in other languages

In your case you duplicate the County names:

=CHOOSE( REPLACE( Entity,1,LEN("Entity"),"")
        , "Peru"
        , "Panama"
        , "Panama"
        , "Panama"
        , "Panama"
        , "Serbia"
        , "South Africa"
        , "Spain"
        , "Spain"
        , "Sweden"
        , "Sweden"
        , "Sweden"
        , "Sweden"
        , "14"
        , "15"
        , "16"
    )

More SharePoint Functions at: http://viewmaster365.com/365coach/#/Calculated_Column_Functions_List

For easy editting you could write the options like

 "7:Spain"

Then process the result in a second column

 =RIGHT( CountyName , FIND(":",CountryName)+1 )

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.