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.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )
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