Concatenate with multiple "IF" functions

[Tuxedo Boston Terriers]

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.

 

[Anne Troy]

=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...

 

[ejamardo]

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.

 

[Aladin Akyurek]

On 2002-11-26 14:18, ejamardo wrote:
[..]
The function concatenate don´t acept formulas , but acept cell addres [...]

Are you that sure?

Book1
	A	B	C	D
1	10	20		1020
Sheet1

 

[IML]

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"

 

[Aladin Akyurek]

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.