Tell me more ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.
up vote 0 down vote favorite

We have a task list with a column that categorizes tasks into one of 12 categories. We're trying to calculate a NEW column that will be populated with one of two values, "Technical" or "Procedural" based on the category.

I can create the column without any difficulties, but keep getting syntax errors when trying to add that column to my site columns.

I'm using nested ifs (=if([category]=condition1),"someValue1",if([category]=condition2),"someValue2",if([etc....) to achieve this but am trying to test with a single if-then, which is also returning syntax errors when I try to add the column to the site.

I suspect I have a misplaced paren/bracket/quote.

=if(([IssueCategory]="Question for IT"),"Technical","other");

Can anyone tell me why this is returning as a Syntax error?

share|improve this question

4 Answers

up vote 3 down vote accepted

As @PirateEric said, you misplaced the parenthesis. If you are still getting an error after that, here are a couple things to check:

  • your category name (is it [IssueCategory] or [Issue Category]?).
  • your category must be a single choice. Multiple choice or lookup won't work

To avoid mistakes, pick the field name from the fields listed in the right column when you write your formula.

share|improve this answer
fieldname is indeed IssueCategory. I cut and paste my field options from the "Type Each Choice on a Seaprate Line" box, and have selected drop-down menu as the display choice. This should give me a single, unique value for the field, right? The nature of the error message--that a reference to a field is an Error, confuses me, because that's what I'm trying to do! – dwwilson66 May 7 '12 at 19:29
Weird. Do you see the field in the list of available fields, next to the box where you enter the formula? – Christophe May 7 '12 at 20:59
That may be my issue, @Chrisophe. I may not be entering the formula in the right field. I'm in Site Settings > Change Site Columns > Additional Comment Settings section > and using the text box under "Default Value", with Calculated Value checked. Is there another place to do this? – dwwilson66 May 8 '12 at 11:10
You're in the wrong place, you need to create a column of type "Calculated Column". In addition, site columns might not have all the fields available. – Christophe May 8 '12 at 16:05
up vote 3 down vote

I believe it should just be =if([IssueCategory]="Question for IT","Technical","other"), the inner paren isn't necessary nor is the semicolon.

share|improve this answer
Error The formula contains reference(s) to field(s). Learn more about the syntax for formulas. Troubleshoot issues with Microsoft SharePoint Foundation. Correlation ID: 244f3a88-6fd2-487d-9c0d-f8fcb5b09ba9 Date and Time: 5/7/2012 1:08:38 PM Go back to site Ummm. Of course it has a reference to fields. That's what I'm trying to do... :) Any other ideas? MS help is sadly lacking... – dwwilson66 May 7 '12 at 18:09
up vote 1 down vote

I am assuming it works if you used only the one item as Eric conveyed it. Is your problem more of nesting the IF statement to get the other items to work? I quickly tested with a choice column with two options and the calculated field and was able to do it as this.

=IF([Choice]="Question for IT","Technical",IF([Choice]="Question for HR","HR","other"))

Oh and not sure if you ran across this article on MSDN, but it has all the basic calculations. http://msdn.microsoft.com/en-us/library/bb862071.aspx

share|improve this answer
up vote 1 down vote

Important All example formulas in this topic use commas "," as the parameter delimiter character. In some countries, the comma is reserved for use as the decimal mark. In such countries, users creating a calculated field must use semi-colons ";" as the delimiter character. Regardless of which character is used when the field is created, the formula works on lists in SharePoint websites anywhere in the world. SharePoint automatically changes the delimiter character to the one that is appropriate for the language/culture of the current page. For example, suppose the following formula is created on a website whose culture setting is fr-fr (France): =IF(Number1>Number2;5;10).

If the website's culture is then changed to en-us (United States), the formula changes automatically to: =IF(Number1>Number2,5,10).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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