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).
--
** John C **

"Steve P" wrote:

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

"Steve P" wrote:

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" wrote:

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