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])))
Improve this question
4
  • =IF(OR([Delivery Status]="Active",[Delivery Status]="Under Development",[Delivery Status]="Under Revision"),[Crs Code]&[Crs Number]&": "&[Crs Title]," ")
    – Ganesh Sanap - Microsoft MVP
    Nov 14, 2018 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, 2018 at 20:13
  • Aren't these Crs Code, Crs Number, Crs Title required fields?
    – Ganesh Sanap - Microsoft MVP
    Nov 15, 2018 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, 2018 at 14:48

2 Answers 2

Reset to default
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]," ")
Improve this answer
2
  • 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, 2018 at 18:01
  • Welcome. More important is you got it working.
    – Ganesh Sanap - Microsoft MVP
    Nov 14, 2018 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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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