file links - arrays???

D

dh

I think there should be a function in excel for what I am tring to do. 20
years ago when I took a little cousre in BASIC we use to call them an array.
Here is what I am tring to do, its like linking to files but I only what to
set up the fumula 1 time. Say I have 2 file - call them file 1 and file 2 -
am working on file 2 - I what a formula like this

=((x)sheet1!A1) and
=((x)Sheet1!A2)

where x is = to C:\file 1.xls

I only want it ienter the "link file", "array" or "x" one time so that it is
easy to change file name in the formula but you don't have to change the
whole formula.

Does any of this make sense? Is this possible?
 
M

Max

Provided the file is open, think you could use INDIRECT

Something like in say, B1:
=INDIRECT("'["&A1&"]Sheet1'!A1")
where you have the filename in A1: file 1.xls

The above will return the same as having in B1:
=[file 1.xls]Sheet1!A1

If file 1.xls is closed, you'll get #REF! error in B1
The error will disappear once you open file 1.xls
 
D

dh

well that does work but is there any wat to do this without opening the
files. I am tring to collect data from many files and the pieces that I need
all reside in the same places on each file

Max said:
Provided the file is open, think you could use INDIRECT

Something like in say, B1:
=INDIRECT("'["&A1&"]Sheet1'!A1")
where you have the filename in A1: file 1.xls

The above will return the same as having in B1:
=[file 1.xls]Sheet1!A1

If file 1.xls is closed, you'll get #REF! error in B1
The error will disappear once you open file 1.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
dh said:
I think there should be a function in excel for what I am tring to do. 20
years ago when I took a little cousre in BASIC we use to call them an array.
Here is what I am tring to do, its like linking to files but I only what to
set up the fumula 1 time. Say I have 2 file - call them file 1 and file 2 -
am working on file 2 - I what a formula like this

=((x)sheet1!A1) and
=((x)Sheet1!A2)

where x is = to C:\file 1.xls

I only want it ienter the "link file", "array" or "x" one time so that it is
easy to change file name in the formula but you don't have to change the
whole formula.

Does any of this make sense? Is this possible?
Click to expand...
Click to expand...
 
Top