Concatenating a range of cells from another sheet, possible?

[rtilghman]

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

 

[Roger Govier]

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

 

[Harlan Grove]

(e-mail address removed) wrote...
....

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,""),)

....

First off, don't mix CONCATENATE and &. At the very least it's
confusing. Better to use & everywhere.

If you won't have any embedded linefeed or ASCII 127 (DEL) chars in
any of these cells' contents, you could try

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(
T('Interview Summary'!$A$77)&CHAR(10)&
T('Interview Summary'!$A$78)&CHAR(10)&
T('Interview Summary'!$A$79)&CHAR(10)&
T('Interview Summary'!$A$80)&CHAR(10)&
T('Interview Summary'!$A$81)&CHAR(10)&
T('Interview Summary'!$A$82)&CHAR(10)&
T('Interview Summary'!$A$83)&CHAR(10)&
T('Interview Summary'!$A$84)&CHAR(10)&
T('Interview Summary'!$A$85)&CHAR(10)&
T('Interview Summary'!$A$86)&CHAR(10)&
T('Interview Summary'!$A$87)&CHAR(10)&
T('Interview Summary'!$A$88)&CHAR(10)&
""," ",CHAR(127)),CHAR(10)," "))," ",CHAR(10)),CHAR(127)," ")

Still ugly, but more easily extended.

 

[Arvi Laanemets]

Hi

What do you really want to do? Your formulas are such a mess I don't have a
quess what you intented to do with them!

CONCANTENATE (or & operator) connects several strings, and returns a string
(NB! Not a cell/range reference!). P.e.
=CONCANTENATE("A","B","C")
, or
="A" & "B" & "C"
returns a string "ABC".
=CONCANTENATE("Sheet1!","A1")
returns a string "Sheet1!A1", not a reference to cell Sheet1!A1, etc.

INDIRECT returns a cell/range reference from a string representing such
reference. P.e.
=INDIRECT("Sheet1A1")
returns a reference to cell Sheet1!A1, i.e. it is same as formula
=Sheet1!A1

The formula
=INDIRECT("Sheet1!A1:A10")
returns an error, because you can't have a direct reference to multicell
range in a cell. But you can use it as range parameter in other functions,
like
=SUM(INDIRECT("Sheet1!A1:A10"))
, which is same as
=SUM(Sheet1!A1:A10)
The formula
=INDIRECT("SUM(Sheet1!A1:A10)")
returns an error, of-course. I think it is obvious why!
NB! The INDIRECT function works with external sources only when the source
workbook is opened. So it will be not very bright idea to write a formula
like:
=INDIRECT("'C:\My Documents\[" & Sheet1!A1 & ".xls]" & Sheet1!B1 & "'!A1")
, and to try to refer to any excel file in folder My Dcuments typing file
name into cell A1 and sheet name into cell B1 - onless the referred file is
opened, you get an error.

ROW returns an integer, representing a row number of cell reference, passed
as parameter (by default the cell where you entered the formula). P.e.
=ROW()
=ROW(A2)
etc. I can't imagine for what it was meant in your formula.

 

[T. Valko]

Off topic.....

=INDEX(A:A............

Does Index index the entire column of just the used range?

Biff

 

[rtilghman]

Hi

What do you really want to do? Your formulas are such a mess I don't have a
quess what you intented to do with them!

Arvi:

Thanks for the post. I think it was actually really clear from my
post (where I described the problem and what I'm trying to
accomplish), but in the event it wasn't let me take another stab.

I have 10 cells in a main sheet in a workbook that may or may not all
have values (depends on the situation). I need a routine that can
cycle through each of these cells, take the value if one exists, build
a list of the values that is hard return delimited, and insert that
list into another cell in another sheet in the workbook. To be blunt
I am essentially trying to replicate simple "For" loop functionality
in an Excel formula.

For example, say I have A1-A10 in Sheet1 and in this instance A1-A6
are filled in. The values are as follow:

Sheet1!A1 - This
Sheet1!A2 - Is
Sheet1!A3 - An
Sheet1!A4 - Example
Sheet1!A5 - Of
Sheet1!A6 - Usage
Sheet1!A7 -
Sheet1!A8 -
Sheet1!A9 -
Sheet1!A10 -

I need a formula I can put in a cell in another worksheet that will go
through these cells to create the following:

Sheet2!C8 -
This
Is
An
Example
Of
Usage

As before, any help anyone can offer in doing this with formulas is
appreciated. Thanks Roger for the pointer on MutiCat, I'm still
trying to do this in Formula for now, but a VB solution may be my only
route.

Best,
rt

 

[Roger Govier]

Hi

In which case, Harlan's reply does exactly what you want.
Remember to format the cell as Wrap text.

 

[rtilghman]

Hi

In which case, Harlan's reply does exactly what you want.
Remember to format the cell as Wrap text.

True, and its appreciated. Sorry Harlan for not including you in my
previous post.

However, I guess I was looking for a way to do this without having to
explicitly call each line... something like a variablized for loop.

Thanks,
Rick