INDIRECT function

[gizmo]

and one more thing:

While working with INDIRECT formula it's easy to make numbers change while
dragging function down the rows, f. ex.
=INDIRECT("'C:\\"&TEXT(ROW(1:1),"00")&"\[anyfile.xls]anysheet'!$A$1") will
give me
C:\\01... in the first row, then C:\\02 in the second etc.

My question is: how should I modify this formula in order to go down not
with numbers, but with letters, f.ex.:
in cell A1 -> C:\\A
in cell A2 -> C:\\B
in cell A3 -> C:\\C

Is this possible?
Many thanks!

Gizmo

 

[Frank Kabel]

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

 

[hgrove]

Frank Kabel wrote...

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

...

Several things.

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

Next, this would work through row 90, which would give Z. Maybe the O
doesn't need to go further down, but just in case s/he does, and if th
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!, i
when the files referenced are already open. If they're already open
there's no need for the pathname because Excel is incapable of openin
multiple files with the same base filename at the same time. So thi
exercise may be wasted effort