locked
SharePoint multiple criteria IF: Concatenate RRS feed

  • Question

  • Hello SharePoint Experts,

    In SP 2007. Have a simple calculated field, which is reading another column then concatenating (actually appending) an asterisk at the end. The calculation is to determine if: based on global or a national location, to add asterisk at end of DocType.

    Something like this: 

    DocType(dropdown):         DocLocation(dropdown):           Disposition (calculated):
    Form                                      Dallas                                          Form
    Memo                                     England                                       Memo
                                                  Global                                         Memo*
                                                  US National                                 Form*
                                                  Europe National                           Form*
                                                  SA National                                 Memo*
                                                  Canada National                          Form*
                                                  Orient National                            Form*

    Basically, I need the calculated field to show the Form or Memo with an asterisk if it's a Global or any sort of "National" location. I've been trying to get the calculated field "Disposition" to work something like this:

    If DocLocation is "Global" OR "US National" OR "Europe National" OR Europe National OR (etc), then CONCATENATE DocType + "*"
    If DocLocation is NOT one of those, then just populate DocType.

    Here's what I've tried (numerous ways)
    =IF([DocLocation]="Global",CONCATENATE([DocType],"*"),IF([DocLocation]<>"GLOBAL",CONCATENATE(DocType,""),IF([DocLocation]="US National",CONCATENATE([DocType],"*"),IF([DocLocation]<>"US National",CONCATENATE([DocType],"")))))

    I can get it to work with just one Location like Global. But, I don't know how to get the field to do all the "OR".
    The second part I'm stuck on is, if Location is NOT, then the system enters "False" in the field.  We need it to just say Form or Memo.

    Any assistance with this is greatly appreciated. Unfortunately, I am not a coder, so am not able to create this deeper nested formula.

    Thanks in advance for any help you can provide!

    Avi

                                                

    Saturday, July 21, 2012 2:03 AM

Answers

  • Hi,

    Try using the this 

    IF(OR([DocLocation]="Global",[DocLocation]="US National",[DocLocation]="Europe National",), CONCATENATE([DocLocation],"*"),[DocLocation])

    I hope this will help you out.


    Thanks, Rahul Rashu

    Saturday, July 21, 2012 6:51 AM

All replies

  • Hi,

    Try using the this 

    IF(OR([DocLocation]="Global",[DocLocation]="US National",[DocLocation]="Europe National",), CONCATENATE([DocLocation],"*"),[DocLocation])

    I hope this will help you out.


    Thanks, Rahul Rashu

    Saturday, July 21, 2012 6:51 AM
  • Hello Rahul,

    Wow! Initial testing of this formula seems to be working. I am going to populate the system with a few more items among the variations to make sure it's reading correctly. But so far, it's looking great!

    Thank you very much. You are a star!

    Sunday, July 22, 2012 5:23 PM