Need alternative to nesting 7 functions, please.

[Morpheosa]

WORKBOOK INFORMATION

MS Excel 2002
Worksheets, A=MEN B=WOMEN C=RESULTS D=EMAIL MESSAGE
Columns, E=ID# F=NAME G=EMAIL ADDRESS

Worksheet "D" needs to pull information from worksheet"C" through the CONCATENATE FUNCTION as well as pull information from either worksheet"A" or worksheet"B". The culled information will be used to create a formatted message.

In worksheet"D" in the first cell I enter:

=CONCATENATE("Dear"," ",Results!B3,":"&CHAR(10)&"You received", " ", Results!AB3," ","results."," ","Please see the list below."&CHAR(10)&CHAR(10)&"ID-",IF(Results!C3="X",Women!A5&" "&Women!B5&" "&Women!D5,""))

and it works. The problem is there are 25 cells across that need the IF FUNCTION to check for the presence or absence of the letter "X" and, apparently, you cannot nest more than 7 functions. I tried the following but there is not a second result even though there should be. The only change that takes place with the second string is the email address in cell Women!D5 is appended by the number 3.

=CONCATENATE("Dear"," ",Results!B3,":"&CHAR(10)&"You received", " ", Results!AB3," ","results."," ","Please see the list below."&CHAR(10)&CHAR(10)&"ID-",IF(Results!C3="X",Women!A5&" "&Women!B5&" "&Women!D5,""),IF(Results!D3="X",Women!A6&" "&Women!B6&" "&Women!D6,""))

The answer to making this work would be great but I would be extremely happy with being pointed in the right direction. I have zero experience with Excel up until five days ago. I never understood the demand for Excel because, to me, Word could do everything that Excel could do but do it a lot easier. BOY WAS I WRONG! The last five days I've been in awe of the things Excel can do.

 

[Morpheosa]

For anyone who comes across this issue there was one huge problem. Super sloppy coding!<smile>. I also came across a bit of useful information on THIS website.

Using the CONCATENATE function

Yet another option was suggested to me by B. Ganesh: Use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function. Here's an example:

=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""), IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,""), IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))</pre> The CONCATENATE function can handle as many as 30 arguments -- which equates to testing 30 different conditions.
And, as Alan Williams pointed out, you can avoid using the CONCATENATE function and use the concatenation operator (&):

=IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"") &IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"") &IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"") &IF(A1="J",10,"")</pre> This method is not limited to 30 comparisons.

 

[Morpheosa]

WORKBOOK INFORMATION

MS Excel 2002
Worksheets, A=MEN B=WOMEN C=RESULTS D=EMAIL MESSAGE
Columns, E=ID# F=NAME G=EMAIL ADDRESS

UGH! I would use a smilie but I don't see one that looks broken and defeated. I fixed all my errors and the formula is exactly what I want it to be but now Excel is refusing the formula claiming, "Formula too long". I did a little research and thought arrays would be the answer to the problem but Excel wont allow me to paste the formula so that I can hit crtrl+shift+enter to create an array. If any one has any ideas how to shorten the formula or force Excel to let me paste so that I can hit those three "magic" keys I would really appreciate it.

Basically if there is an "X" in the "Men" cell in "Results" it retrieves information from the "Women" cell in worksheetB and lists it in "Email Message" in worksheetD. If there is not an "X" the cell is ignored.

=CONCATENATE("Dear"," ",Results!B3,":"&CHAR(10)&CHAR(10)&"You received"," ",Results!AB3," ","results."," ","Please see the results below."&CHAR(10)&CHAR(10)&IF(Results!C3="X","ID-"&Women!A5&" "&Women!B5&" "&Women!D5,""&CHAR(10)&)IF,(Results!D3="X","ID-"&Women!A6&" "&Women!B6&" "&Women!D6,""&CHAR(10)&)IF,(Results!E3="X","ID-"&Women!A7&" "&Women!B7&" "&Women!D7,""&CHAR(10)&)IF,(Results!F3="X","ID-"&Women!A8&" "&Women!B8&" "&Women!D8,""&CHAR(10)&)IF,(Results!G3="X","ID-"&Women!A9&" "&Women!B9&" "&Women!D9,""&CHAR(10)&)IF,(Results!H3="X","ID-"&Women!A10&" "&Women!B10&" "&Women!D10,""&CHAR(10)&)IF,(Results!I3="X","ID-"&Women!A11&" "&Women!B11&" "&Women!D11,""&CHAR(10)&)IF,(Results!J3="X","ID-"&Women!A12&" "&Women!B12&" "&Women!D12,""&CHAR(10)&)IF,(Results!K3="X","ID-"&Women!A13&" "&Women!B13&" "&Women!D13,""&CHAR(10)&)IF,(Results!L3="X","ID-"&Women!A14&" "&Women!B14&" "&Women!D14,""&CHAR(10)&)IF,(Results!M3="X","ID-"&Women!A15&" "&Women!B15&" "&Women!D15,""&CHAR(10)&)IF,(Results!N3="X","ID-"&Women!A16&" "&Women!B16&" "&Women!D16,""&CHAR(10)&)IF,(Results!O3="X","ID-"&Women!A17&" "&Women!B17&" "&Women!D17,""&CHAR(10)&)IF,(Results!P3="X","ID-"&Women!A18&" "&Women!B18&" "&Women!D18,""&CHAR(10)&)IF,(Results!Q3="X","ID-"&Women!A19&" "&Women!B19&" "&Women!D19,""&CHAR(10)&)IF,(Results!R3="X","ID-"&Women!A20&" "&Women!B20&" "&Women!D20,""&CHAR(10)&)IF,(Results!S3="X","ID-"&Women!A21&" "&Women!B21&" "&Women!D21,""&CHAR(10)&)IF,(Results!T3="X","ID-"&Women!A22&" "&Women!B22&" "&Women!D22,""&CHAR(10)&)IF,(Results!U3="X","ID-"&Women!A23&" "&Women!B23&" "&Women!D23,""&CHAR(10)&)IF,(Results!V3="X","ID-"&Women!A24&" "&Women!B24&" "&Women!D24,""&CHAR(10)&)IF,(Results!W3="X","ID-"&Women!A25&" "&Women!B25&" "&Women!D25,""&CHAR(10)&)IF,(Results!X3="X","ID-"&Women!A26&" "&Women!B26&" "&Women!D26,""&CHAR(10)&)IF,(Results!Y3="X","ID-"&Women!A27&" "&Women!B27&" "&Women!D27,""&CHAR(10)&)IF,(Results!Z3="X","ID-"&Women!A28&" "&Women!B28&" "&Women!D28,""&CHAR(10)&)IF,(Results!AA3="X","ID-"&Women!A29&" "&Women!B29&" "&Women!D29,"")&CHAR(10)&CHAR(10)&"Thank you for participating")

 

[Sandeep Warrier]

Hi & welcome to the Board!

So basically, if there is an "x" in Results B3-X3, then return the corresponding values from Women A5 - C26

Here are two methods:

1.

=INDEX(Women!$A$5:$A$26,MATCH("x",Results!$C$3:$X$3,0))&" "&INDEX(Women!$B$5:$B$26,MATCH("x",Results!$C$3:$X$3,0))&" "&INDEX(Women!$C$5:$C$26,MATCH("x",Results!$C$3:$X$3,0))

2. In Excel, press Alt+F11. In the new window that opens up, press Alt+I+M and paste the code given below in the white area on the right. Close the new window and in Excel, use the formula given below the code.

Function RangeConcat(rngInput As Range, Optional Delim As String = " ") As String
    Dim Temp As String
    Dim rCell As Range
        
    For Each rCell In rngInput
        Temp = Temp & rCell.Value & Delim
    Next rCell
    
    RangeConcat = Left$(Temp, Len(Temp) - Len(Delim))
End Function

=RangeConcat(INDEX(Women!$A$5:$C$26,MATCH("x",Results!$C$3:$X$3,0),0))

So, basically your full formula would be

1.

=CONCATENATE("Dear"," ",Results!B3,":"&CHAR(10)&CHAR(10)&"You received"," ",Results!AB3," ","results."," ","Please see the results below."&CHAR(10)&CHAR(10)&INDEX(Women!$A$5:$A$26,MATCH("x",Results!$C$3:$X$3,0))&" "&INDEX(Women!$B$5:$B$26,MATCH("x",Results!$C$3:$X$3,0))&" "&INDEX(Women!$C$5:$C$26,MATCH("x",Results!$C$3:$X$3,0))&CHAR(10)&CHAR(10)&"Thank you for participating")

Or.

2.

=CONCATENATE("Dear"," ",Results!B3,":"&CHAR(10)&CHAR(10)&"You received"," ",Results!AB3," ","results."," ","Please see the results below."&CHAR(10)&CHAR(10)&RangeConcat(INDEX(Women!$A$5:$C$26,MATCH("x",Results!$C$3:$X$3,0),0))&CHAR(10)&CHAR(10)&"Thank you for participating")

 

[Sal Paradise]

Formulas can only be up to 255 characters before Excel forbids you from using them.

One way to do it is names.

Go to insert -> define -> name and create a formula for each "IF"

Part1 =CONCATENATE("Dear"," ",Results!B3,":"&CHAR(10)&CHAR(10)&"You received"," ",Results!AB3," ","results."," ","Please see the results below."&CHAR(10)&CHAR(10))
Part2=IF(Results!C3="X","ID-"&Women!A5&" "&Women!B5&" "&Women!D5,""&CHAR(10)&)
Part3=IF,(Results!D3="X","ID-"&Women!A6&" "&Women!B6&" "&Women!D6,""&CHAR(10)&)
Part4=IF,(Results!E3="X","ID-"&Women!A7&" "&Women!B7&" "&Women!D7,""&CHAR(10)

...

LastPart=CHAR(10)&CHAR(10)&"Thank you for participating")

Then in the cell put the formula: =CONCATENATE(Part1,Part2,Part3,Part4,...,LastPart)

That should work without using VBA.

 

[Morpheosa]

Sandeep and Sal:

THANK YOU SO MUCH!!! You are life savers. I tried Sandeep's suggestion first but I've never even opened Excel until a little over a week ago. I somehow messed the VB stuff up pretty good.

Sal's suggestion of the NAMES ended up being simpler for me to make work. I just want to note for anyone who may read this in the future that I did have to change a few things. In theory Sal's example below should have worked perfectly (from my extremely limited perspective) but it would not work as written. When using NAMES you need to also use dollar signs to express a constant (i.e., A1 becomes $A$1). Even after sorting that out the formula would show in the cell but not the output. I had to change the formula by adding COUNTA and referencing the worksheets directly rather than from the Results page.

IF(COUNTA(Men!$C$3,Women!$C$3)=2,"ID-"&Women!$A$5&" "&Women!$B$5&" "&Women!$D$5,""&CHAR(10)&)

I think it is because I am using text to distinguish the cell instead of a numerical value but I don't know for sure.

Again, THANK YOU GUYS SO MUCH!!! It would have taken me forever to even guess which way to go after I hit that Excel error.

Ahhh yes, a suitable smilie. ray:

 

[Sal Paradise]

Glad it worked out.