Concatenation frustrations....

[Steve P]

Hey everyone,

I'm writing a nested IF statement that will evaluate a particular cell in a
given row and concatenate a series of other cells in that same row. However,
I can't seem to get the formula to work...here's the code:

=IF(L8="Open", AE + " - " +AF + CHAR(10) + CHAR(10) AG + " - " +AH +
CHAR(10) + CHAR(10) AI + " - " +AJ + CHAR(10) + CHAR(10) AK + " - " +AL +
CHAR(10) + CHAR(10) AM + " - " +AN, IF(L8="Re-opened", AE + " - " +AF +
CHAR(10) + CHAR(10) AG + " - " +AH + CHAR(10) + CHAR(10) AI + " - " +AJ +
CHAR(10) + CHAR(10) AK + " - " +AL + CHAR(10) + CHAR(10) AM + " - " +AN, ""))


I keep getting a "name" error. What the code above is doing is first
checking for rows that are designated as "open". If the statement is true,
its supposed to concatentate various other cells in the row and do an
alt+enter to separate some of the concatenations. When a row ISN'T
designated as "open", it reevaluates the original cell (L8) and then checks
to see if the row has been designated as "Re-opened". It then performs the
same concatenation.

What am I doing wrong here? Is this simply a syntax issue, or am I being
too ambitious concatenating? I'm really trying to save myself the hassle of
manually copying and pasting all 150-200 records in this spreadsheet...please
help!

~Steve P

 

[John C]

Without actually checking the formula, I noticed you have CHAR(10)+CHAR(10)
in multiple spots, but you have nothing between the 2nd closing parentheses
and, for example, AG then AK, then AE, then AI, and finally AM.

Secondly, unless the following are variables: AE, AF, AG, AH, AI, AJ, AK,
AL, AM, and AN, then you still need a row reference for each of them,
otherwise they are only columns. And with no row reference, Excel thinks they
are undefined variables.

 

[John C]

Oh, and finally, I would use & instead of +.

 

[David Biddulph]

Yes, you've got your syntax seriously wrong.
Concatenation uses the symbol &, not + which you've sometimes used and
sometimes omitted.
If you want to include the text AF, you need to enclose it in quote marks
"AF"; if instead of that you intended to include the content of a cell, you
need to give the reference of the cell, not just the row identifier, so
perhaps you intended somnething like AF8, rather than AF?

 

[Steve P]

ok...i just realized how off my code was...no cell references! Here's the
revised code however i'm still getting errors

=IF(L42="Open", AE42 + " - " +AF42 + CHAR(10) + CHAR(10) AG42 + " - " +AH42
+ CHAR(10) + CHAR(10) AI42 + " - " +AJ42 + CHAR(10) + CHAR(10) AK42 + " - "
+AL42 + CHAR(10) + CHAR(10) AM42 + " - " +AN42, IF(L42="Re-opened", AE42 + "
- " +AF42 + CHAR(10) + CHAR(10) AG42 + " - " +AH42 + CHAR(10) + CHAR(10) AI42
+ " - " +AJ42 + CHAR(10) + CHAR(10) AK42 + " - " +AL42 + CHAR(10) + CHAR(10)
AM42 + " - " +AN42, ""))

 

[Steve P]

John and Dave, THANKS for your responses....
I added correct cell references and used "&" instead of "+" for
concatenating and got the formula working. Here's my revised/working code:

=IF(L42="Open", AE42 & " - " &AF42 & CHAR(10) & CHAR(10) & AG42 & " - "
&AH42 & CHAR(10) & CHAR(10) & AI42 & " - " &AJ42 & CHAR(10) & CHAR(10) & AK42
& " - " &AL42 & CHAR(10) & CHAR(10) & AM42 & " - " &AN42, IF(L42="Re-opened",
AE42 & " - " &AF42 & CHAR(10) & CHAR(10) & AG42 & " - " &AH42 & CHAR(10) &
CHAR(10) & AI42 & " - " &AJ42 & CHAR(10) & CHAR(10) & AK42 & " - " &AL42 &
CHAR(10) & CHAR(10) & AM42 & " - " &AN42, ""))


The only thing now is, I only want the concatenation to occur when there is
something in the cells to concatenate. The formula adds extra spaces and
extra
" - "s even when nothing is available to concatenate on either side of the
dash. Is there a relatively easy way to accomplish this without nesting
several other IF statements into the formula?



John

 

[Keith Faulconer]

Hey Steve,

I am not sure if this is what you are trying to do. Are the AEs and AFs
supposed to be cell references? If so, add in the row function as well.
Also, I don't know if this is just a copy/paste problem, the + should be &.
I copied/pasted your formula into my excel and got the name error. The AN
reference did not have a row reference, I didn't add a 1 to AN. After I did
that, it returned " - - - - - - - ". Here is a copy of the formula. Also,
one other note to make the formula easier, try doing,
=if(or(L8="Open",L8="Reopened").... then enter your criteria, you only have
to enter that big long line of text once. I copied an example of both.

Hope this helps.

=IF(L9="Open", (AE2&" - "&AF2&CHAR(10)&CHAR(10)&AG2&" -
"&AH2&CHAR(10)&CHAR(10)&AI2&" - "&AJ2&CHAR(10)&CHAR(10)&AK2&" -
"&AL2&CHAR(10)&CHAR(10)&AM2&" - "&AN2),IF(L9="Re-opened", (AE2&" -
"&AF2&CHAR(10)&CHAR(10)&AG2&" - "&AH2&CHAR(10)&CHAR(10)&AI2&" -
"&AJ2&CHAR(10)&CHAR(10)&AK2&" - "&AL2&CHAR(10)&CHAR(10)&AM2&" - "&AN2),""))

=IF(OR(L10="Open",L10="Re-opened"),(AE3&" -
"&AF3&CHAR(10)&CHAR(10)&AG3&" - "&AH3&CHAR(10)&CHAR(10)&AI3&" -
"&AJ3&CHAR(10)&CHAR(10)&AK3&" - "&AL3&CHAR(10)&CHAR(10)&AM3&" - "&AN3),"")

 

[John C]

I think this formula will accomplish what you are wanting:

=IF(RIGHT(L8,4)<>"Open","",IF(AND(AE42="",AF42=""),"",AE42&" -
"&AF42&IF(AND(AG42="",AH42="",AI42="",AJ42="",AK42="",AL42="",AM42="",AN42=""),"",REPT(CHAR(10),2)))&IF(AND(AG42="",AH42=""),"",AG42&"
-
"&AH42&IF(AND(AI42="",AJ42="",AK42="",AL42="",AM42="",AN42=""),"",REPT(CHAR(10),2)))&IF(AND(AI42="",AJ42=""),"",AI42&"
-
"&AJ42&IF(AND(AK42="",AL42="",AM42="",AN42=""),"",REPT(CHAR(10),2)))&IF(AND(AK42="",AL42=""),"",AK42&"
-
"&AL42&IF(AND(AM42="",AN42=""),"",REPT(CHAR(10),2)))&IF(AND(AM42="",AN42=""),"",AM42&" - "&AN42))

If there are other instances ending in Open for cell L8, you could change
the current
RIGHT(L8,4)="Open"
to
OR(L8="Open",L8="Re-Open")

In addition. It will only display data if either of each 2 cells has data
(for example, for the AE42-AF42 to display, one of them must show data. Also,
it will only do the double carriage return if you have data after the
currently displayed data, and it will start only where there is data (so if
AE42 and AF42 are both blank, then it will display neither, and it also wont
do the double carriage return).

 

[Steve P]

Hey guys...i finally got it working. Since all of you helped, i'm trying to
make sure you all get credit for the assist.

Thanks again!