Calculated column formula: IF THEN statement?

9566 views
Skip to first unread message

Erik

unread,
Jun 5, 2006, 7:00:01 PM6/5/06
to
In a SharePoint 2003 List is it possible to use an IF THEN statement (or
equivalent) to auto populate a given text column based off another text
column's value? Here's my scenario. I run a Sales group and each of my Sales
Reps are assigned to a specific US territory (either East, West, North or
South). Column1 is a drop down menu with the following 4 choices following:
East, West, North or South. I’d like Column5 to be auto populated with the
appropriate Sales Rep’s name based off the value in Column1. For example:

If Column1 = East then Column5 = John Smith. If Column1 = West then Column5
= Roger Brown. If Column1 = North then Column5 = Jack Tate. If Column1 =
South then Column5 = Jim Black.

I looked all over SharePoint help and it doesn't appear this particular
formula is a feature of SharePoint, but I just want to double check. Any
idea? Thanks.

erik

Philip Lanier

unread,
Jun 6, 2006, 12:13:07 PM6/6/06
to
Good news... this does exist. Syntax is as follows:

IF(logical_test,value_if_true,value_if_false)

You can get to this in the online help by at
http://[servername]/_vti_bin/help/1033/sts/html/lif4.htm. For instance,
here's one currently available online:
http://usa1021.wss.bcentral.com/_vti_bin/help/1033/sts/html/lif4.htm. How I
got there. [In the list] "Modify Settings and columns >> Add a new column
>> [Optional Settings for Column group] Show me more information >>
Calculated >> About computed and calculated columns >> [Related Topics]
Functions"

Hope this is useful!

Philip Lanier
Ascentium


"Erik" <Er...@discussions.microsoft.com> wrote in message
news:E085D3B7-A6BB-427B...@microsoft.com...

S.S. Ahmed

unread,
Jun 6, 2006, 7:15:44 PM6/6/06
to
Hi Erik

Yes, it is possible to use IF ELSE in calculated field. Use following
syntax:

=IF([Column1]="East","John Smith",IF([Column1]="West","Roger
Brown",IF([Column1]="North","Jack Tate",IF([Column1]="South","Jim Black"))))

HTH

Regards,

--SSA
http://sharepointblogs.com/ssa (All About SharePoint)

-----

"Erik" <Er...@discussions.microsoft.com> wrote in message
news:E085D3B7-A6BB-427B...@microsoft.com...

Erik

unread,
Jun 8, 2006, 6:50:01 PM6/8/06
to
Perfect - thanks, Philip and SS!

Rene

unread,
Apr 22, 2009, 12:34:07 PM4/22/09
to
Same scenario, but formula gives an error after 7 IF statements. Is there
another formula I can use, since I have 11 regions.

a.qal...@gmail.com

unread,
Jan 14, 2014, 1:19:07 AM1/14/14
to
HI every body
I have a problem with IF condition in sharepoint2007,I want to show delay between two days so first of all I created a list that has 4 column (title,date created, end date , and delay)in delay column i want to use this if condition:
=IF([end]-[create]>=0,0,1)
but it returns following error:
The formula contains a syntax error or is not supported.

Can anybody help me ?
thanks

michael.h...@gmail.com

unread,
Oct 18, 2017, 11:31:47 AM10/18/17
to
Hello!

I have an issue where I need to copy data from one column to another. My ex:
=RIGHT([Schedule Identifier - New],LEN([Schedule Identifier - New])-FIND("#",[Schedule Identifier - New])). In this example, if the column name has a #, it copies it over to another column minus the data to the left of the #. The issue is, if the data doesn't have a #, then it is not copying over and just labeling data in column as #NAME?, Any suggestions to fix this?
Reply all
Reply to author
Forward
0 new messages