0

I have created a Calculated Column that combines 3 other columns. The Formula for that is

=[Crs Code]&[Crs Number]&": "&[Crs Title]

And this works.

Now I want to do the same thing, but only if certain conditions are true.

The result needs to be =[Crs Code]&[Crs Number]&": "&[Crs Title], but only if the column [Delivery Status]= Active OR Under Development OR Under Revision. If none of those conditions are true, then move on to the next item for processing.

I tried all of the Formulas below, but all give syntax errors probably because IF statements in calculated columns require a TRUE and FALSE response. But, I don't know what to enter for the negative response, if all I am looking for is a positive response.

=IF(OR([Delivery Status]='Active',[Delivery Status]='Under Development',[Delivery Status]='Under Revision',[Crs Code]&[Crs Number]&": "&[Crs Title],""))

=IF(OR([Delivery Status]='Active',[Delivery Status]='Under Development',[Delivery Status]='Under Revision',[Crs Code]&[Crs Number]&": "&[Crs Title]))

=IF(OR([Delivery Status]='Active',[Delivery Status]='Under Development',[Delivery Status]='Under Revision'),[Crs Code]&[Crs Number]&": "&[Crs Title])

=IF(([Delivery Status]='Active',OR([Delivery Status]='Under Development'),OR([Delivery Status]='Under Revision')),([Crs Code]&[Crs Number]&": "&[Crs Title])

=IF(OR([Delivery Status]='Active',OR([Delivery Status]='Under Development'),OR([Delivery Status]='Under Revision')),([Crs Code]&[Crs Number]&": "&[Crs Title])

=IF([Delivery Status]='Active'),[Crs Code]&[Crs Number]&": "&[Crs Title],=IF([Delivery Status]='Under Development'),[Crs Code]&[Crs Number]&": "&[Crs Title],=IF([Delivery Status]='Under Revision'),[Crs Code]&[Crs Number]&": "&[Crs Title]

=IF([Delivery Status]='Active',[Crs Code]&[Crs Number]&": "&[Crs Title],=IF([Delivery Status]='Under Development',[Crs Code]&[Crs Number]&": "&[Crs Title],=IF([Delivery Status]='Under Revision',[Crs Code]&[Crs Number]&": "&[Crs Title])))
  • =IF(OR([Delivery Status]="Active",[Delivery Status]="Under Development",[Delivery Status]="Under Revision"),[Crs Code]&[Crs Number]&": "&[Crs Title]," ") – Ganesh Sanap Nov 14 '18 at 17:32
  • Now the column shows several blank lines. Is there a way to hide or remove those lines those lines after the column is populated, or while it is being populated? – mappingman Nov 14 '18 at 20:13
  • Aren't these Crs Code, Crs Number, Crs Title required fields? – Ganesh Sanap Nov 15 '18 at 2:55
  • Crs Code, Crs Number, Crs Title are being use to create the CC. The CC ends up with several hundred blank entries. I didn't explain my problem fully in my original post. I am using this column in another list as a CHOICE column with a data source. I was able to reduce the number of blank entries to 1 by enabling unique values, but still have that one list item. Not a huge deal if I make it 'zzz' so it goes to the bottom, but not ideal. – mappingman Nov 15 '18 at 14:48
0

The Third formula you are using is quite accurate, just need to make some modifications. Try using below formula:

=IF(OR([Delivery Status]="Active",[Delivery Status]="Under Development",[Delivery Status]="Under Revision"),[Crs Code]&[Crs Number]&": "&[Crs Title]," ")
  • 1
    Thanks Ganesh! I wish I could set both answers as correct. But since mike provided the Excel tool, I gave it to him. ;-) – mappingman Nov 14 '18 at 18:01
  • Welcome. More important is you got it working. – Ganesh Sanap Nov 14 '18 at 18:08
1

You have the right idea, but the parens are in the wrong places. ORs do return a True or False.

=IF(
    OR([Delivery Status]='Active',
       [Delivery Status]='Under Development',
       [Delivery Status]='Under Revision'
   ),
  [Crs Code]&[Crs Number]&": "&[Crs Title],
  ""
 )

or with some comments:

=IF(                                          the IF statement
    OR([Delivery Status]='Active',            the True/False test
       [Delivery Status]='Under Development',    (if any of these
       [Delivery Status]='Under Revision'         are true...)
   ),                                         end of the OR
  [Crs Code]&[Crs Number]&": "&[Crs Title],   what to return if True
  ""                                          what to return if False
 )                                            end of the IF

The easiest way to write and debug these formulas is by using Excel: http://techtrainingnotes.blogspot.com/2010/08/sharepoint-creating-calculated-column.html

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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