Follow up on my previous "INDIRECT formula" question

[gizmo]

OK guys, mea culpa - I took out the INDIRECT.EXT in order not to complicate
additionally my question and forgot that it blows up the whole logic of my
statement.

Anyway, I tried to follow your suggestions, but I'm still commiting
somewhere an error with the CHAR(ROW(1:1)) case, even if I simplified my
studycase.(hgrove >'s proposal to use
SUBSTITUTE(ADDRESS(1,ROW(1:1),4),"1","") statement works extremely fine!).
Do you have an idea why (in a simplified case where I just want to retrieve
the data from the other worksheet of the same workbook with the help of
INDIRECT function)

=INDIRECT("Summary!"&CHAR(ROW(1:1))&"38") is not working, while
=INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1);4);"1";"")&"38") does?

And one more question (just to satisfy my curiosity). How should I modify my
functions if one day I would like to present retrieved data:

=INDIRECT("Summary!A"&TEXT(ROW(15:15);"00")) i.e. retrieve columns
(from Sheet X) in columns (on Sheet Y)
=INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1);4);"1";"")&"15")
i.e. retrieve lines (from Sheet X) in columns (on Sheet Y)

not in columns, but in rows? I know that I can always use copy/paste
special/transpose, but this solution seems to me to be a little bit stupid
and counterproductive.

Once again, thank you guys for your will of sharing with us a small part of
your expertise!!

Gizmo

------------

Frank Kabel wrote...

try
=INDIRECT("'C:\\"&CHAR(ROW(65:65)) &"\[anyfile.xls]anysheet'!$A$1")

...

hgrove > wrote...

Several things.

First, double backslashes in pathnames are only used at the beginning
of UNC pathnames, not pathnames in mapped drives. Even if everything
else in the formula above were perfect, this would cause the formula to
return #REF!.

Next, this would work through row 90, which would give Z. Maybe the OP
doesn't need to go further down, but just in case s/he does, and if the
entries after Z would be AA, AB, etc., then safer to use

=INDIRECT("'C:\"&SUBSTITUTE(ADDRESS(1,ROW(1:1),4),"1","")
&"\[anyfile.xls]anysheet'!$A$1")

Note also that the only way this works, so returns other than #REF!, is
when the files referenced are already open. If they're already open,
there's no need for the pathname because Excel is incapable of opening
multiple files with the same base filename at the same time. So this
exercise may be wasted effort.

 

[Frank Kabel]

gizmo wrote:
[...]

Anyway, I tried to follow your suggestions, but I'm still commiting
somewhere an error with the CHAR(ROW(1:1)) case, even if I simplified
my studycase.(hgrove >'s proposal to use
SUBSTITUTE(ADDRESS(1,ROW(1:1),4),"1","") statement works extremely
fine!). Do you have an idea why (in a simplified case where I just
want to retrieve the data from the other worksheet of the same
workbook with the help of INDIRECT function)

=INDIRECT("Summary!"&CHAR(ROW(1:1))&"38") is not working, while
=INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1);4);"1";"")&"38")
does?

In the first formula the part
CHAR(ROW(1:1))&"38"
returns the ASCII code 1 (which is not the same as 'A'). Just type the
formula
=CHAR(1)
in your worksheet and look at the result
You could achieve your result (at least for the characters A-Z) with
the statement
INDIRECT("Summary!"&CHAR(ROW(65:65))&"38")
as CHAR(65) = 'A'


To explain Harlan's solution which is more robust as it also covers
columns behind 'Z' first have a look at the part:
ADDRESS(1;ROW(1:1);4)
This returns the cell address in a relative format. the first parameter
is the row index (always 1) and the second parameter is the column
index (here the ROW function is used to return columns starting with
column 1 - x)
So the result of this formula if copied down would be:
A1
B1
C1
D1
....

Now the next thing is to remove the row index '1' . this is what
SUBSTITUTE does. It removes the number '1'. Afterwards your desired
row_index '38' is added.
Saying this you can shorten the above formula to:
=INDIRECT("Summary!"&ADDRESS(38;ROW(1:1);4))

And one more question (just to satisfy my curiosity). How should I
modify my functions if one day I would like to present retrieved data:

=INDIRECT("Summary!A"&TEXT(ROW(15:15);"00")) i.e. retrieve
columns (from Sheet X) in columns (on Sheet Y)
=INDIRECT("Summary!"&SUBSTITUTE(ADDRESS(1;ROW(1:1);4);"1";"")&"15")
i.e. retrieve lines (from Sheet X) in columns (on Sheet Y)

For returning this in columns try:
=INDIRECT("Summary!"&ADDRESS(15;COLUMN(A:A);4))


Frank

 

[gizmo]

Thank you very much Frank, once again, for your help and your illuminating
comments.
I hope that one day i will also be able to contribute to this newsgroup and
help some other ppl like you do!

Have a great time,

Gizmo

gizmo wrote:
[...]

In the first formula the part
CHAR(ROW(1:1))&"38"
returns the ASCII code 1 (which is not the same as 'A'). Just type the
formula
=CHAR(1)
in your worksheet and look at the result
You could achieve your result (at least for the characters A-Z) with
the statement
INDIRECT("Summary!"&CHAR(ROW(65:65))&"38")
as CHAR(65) = 'A'

[...]