Hi

Take a look at JE MCGimpsey's Muticat code.
That might give you a start in trying to achieve what you want.
http://www.mcgimpsey.com/excel/udfs/multicat.html

--
Regards

Roger Govier


wrote in message
ups.com...

Okay, so I've got an XLS I've written to handle interview data for a
project I'm working on. One of the aspects of this is that I need to
take a group of cells defined in one location, concatenate them
together on different lines in the same cell, and insert that in
multiple spots to serve as a reference.

To date I've come up with zilch in terms of automating this using some
conbination of CONCATENATE, INDIRECT, and ROW (which was the solution
I thought might get me there).
A solution that I tried but that failed is the following:

CONCATENATE(INDIRECT("'"&"Interview
Summary"&"'!"$A"ROW(INDIRECT("77:88")))

Which always ended up resolving to 77 and giving me a single value.
The following is the ugly solution I'm currently leveraging:

=CONCATENATE(
IF(ISTEXT('Interview Summary'!$A$77),'Interview Summary'!$A$77,""),
IF(ISTEXT('Interview Summary'!$A$78),CHAR(10)&'Interview Summary'!$A
$78,""),
IF(ISTEXT('Interview Summary'!$A$79),CHAR(10)&'Interview Summary'!$A
$79,""),
IF(ISTEXT('Interview Summary'!$A$80),CHAR(10)&'Interview Summary'!$A
$80,""),
IF(ISTEXT('Interview Summary'!$A$81),CHAR(10)&'Interview Summary'!$A
$81,""),
IF(ISTEXT('Interview Summary'!$A$82),CHAR(10)&'Interview Summary'!$A
$82,""),
IF(ISTEXT('Interview Summary'!$A$83),CHAR(10)&'Interview Summary'!$A
$83,""),
IF(ISTEXT('Interview Summary'!$A$84),CHAR(10)&'Interview Summary'!$A
$84,""),
IF(ISTEXT('Interview Summary'!$A$85),CHAR(10)&'Interview Summary'!$A
$85,""),
IF(ISTEXT('Interview Summary'!$A$86),CHAR(10)&'Interview Summary'!$A
$86,""),
IF(ISTEXT('Interview Summary'!$A$87),CHAR(10)&'Interview Summary'!$A
$87,""),
IF(ISTEXT('Interview Summary'!$A$88),CHAR(10)&'Interview Summary'!$A
$88,""),
)

Is it possible to do this in a better, more automated way? Excel's
function language is extremely annoying (I could write this in js,
perl, or any number of languages in about five seconds). Does anyone
have any idea how I could implement a more universal version of this
that would be easier to update, etc.?

Thanks in advance for any help.

-rt