Results 1 to 6 of 6

Concatenate with multiple "IF" functions

This is a discussion on Concatenate with multiple "IF" functions within the Excel Questions forums, part of the Question Forums category; I am trying to establish a form letter that uses multiple choices from a data area to fill in blanks ...

  1. #1
    New Member
    Join Date
    Nov 2002
    Location
    McKinney, Texas
    Posts
    5

    Default

    I am trying to establish a form letter that uses multiple choices from a data area to fill in blanks within a text string. My data would be something like this:

    A1 = Individual
    A2 = Corporation
    A3 = Municipality

    A6 = Mr.
    A7 = John
    A8 = Doe ( these three constitute customer's name)
    A9 = ACME COMPANY
    A10 = City of Dallas

    Using a drop-down box I can choose from:
    A20 = Individual
    Corporation
    Municipality

    Now my problem arises....I get lost in the multiple "IF" functions. I am trying to come up with something like this to be used in a text string :

    If A20 = Individual, Then A6 A7 A8 (A proper name)
    If A20 = Corporation, Then A9
    If A20 = Municipality, Then A10

    The above information would be used in a text string to appear such as:

    This Agreement is made between MY COMPANY and Mr. John Doe, an Individual, each hereinafter.......

    or

    This agreement is made between MY COMPANY and ACME COMPANY, A Corporation, each hereinafter........

    or

    This agreement is made between MY COMPANY and the City of Dallas, A Municipality, each hereinafter.....

    I know this is not correct but the idea of what I want to do is representated below:

    =CONCATENATE("This agreement is made between My Company and ", IF(A20=A1, Then A6 A7 A8)IF(A20=A2,Then A9) IF(A20=A3,Then A10),",a ",If(A20=A1,A1)IF(A20=A2,A2)IF(A20=A3,A3),", each hereinafter as blah blah blah.")

    I would appreciate any help in getting the IF's and the commas all in the right place. So far I've not been sucessful in doing so.

    Thank you in advance...and have a wonderful Holiday Season.



  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Allentown, PA
    Posts
    2,543

    Default

    =IF(a20=A1,"This agreement is made between My Company and "&A6&" "&A7&" "&A8,(a20=A2,"This agreement is made between My Company and "&A6&" "&A7&" "&A9,if(a20=A3,"This agreement is made between My Company and "&A6&" "&A7&" "&A10...
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Nov 2002
    Location
    Brazil
    Posts
    3

    Default

    In my opinion you should use vlookup instead of if . Create an array with all the possibilities .
    The function concatenate donīt acept formulas , but acept cell addres , so you should calculate with vlookup and in another cell concatenate.

    Sorry if I coul not explain better.


  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,929

    Default

    On 2002-11-26 14:18, ejamardo wrote:
    [..]
    The function concatenate donīt acept formulas , but acept cell addres [...]
    Are you that sure?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    1020*10*20
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


  5. #5
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    Another option, choose:

    ="This agreement is made between my company and "&CHOOSE(MATCH(A20,{"individual";"corporation";"municipality"},0),A6&" "&A7&" "&A8,A9,A10)&", a"&IF(A20="individual","n "," ")&A20&", each referred to blah blah blah"

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,929

    Default

    On 2002-11-26 13:34, Tuxedo Boston Terriers wrote:
    I am trying to establish a form letter that uses multiple choices from a data area to fill in blanks within a text string. My data would be something like this:

    A1 = Individual
    A2 = Corporation
    A3 = Municipality

    A6 = Mr.
    A7 = John
    A8 = Doe ( these three constitute customer's name)
    A9 = ACME COMPANY
    A10 = City of Dallas

    Using a drop-down box I can choose from:
    A20 = Individual
    Corporation
    Municipality

    Now my problem arises....I get lost in the multiple "IF" functions. I am trying to come up with something like this to be used in a text string :

    If A20 = Individual, Then A6 A7 A8 (A proper name)
    If A20 = Corporation, Then A9
    If A20 = Municipality, Then A10

    The above information would be used in a text string to appear such as:

    This Agreement is made between MY COMPANY and Mr. John Doe, an Individual, each hereinafter.......

    or

    This agreement is made between MY COMPANY and ACME COMPANY, A Corporation, each hereinafter........

    or

    This agreement is made between MY COMPANY and the City of Dallas, A Municipality, each hereinafter.....

    I know this is not correct but the idea of what I want to do is representated below:

    =CONCATENATE("This agreement is made between My Company and ", IF(A20=A1, Then A6 A7 A8)IF(A20=A2,Then A9) IF(A20=A3,Then A10),",a ",If(A20=A1,A1)IF(A20=A2,A2)IF(A20=A3,A3),", each hereinafter as blah blah blah.")

    I would appreciate any help in getting the IF's and the commas all in the right place. So far I've not been sucessful in doing so.

    Thank you in advance...and have a wonderful Holiday Season.


    Refine...

    ="This agreement is made between My Company and "&IF(A20=A1,A6&" "&A7&" "&A8,IF(A20=A2,A9,IF(A20=A3,A10,"")))&",a "&VLOOKUP(A20,A1:A3,1,0)&", each hereinafter as blah blah blah."

    if needed.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com