Dynamic External File Refrence?

[hamed_gan]

Dear all,

I have the following question;

Is it possible in Excel, when typing in your external path to you
external file, to use name references given in other cells (and thu
make it "dynamic")? So, let's say I have the following situation;

Uhave 3 colums, in which the data are meant for 3 different months;

Jan-2004.......................Feb-2004.........................Mar-2004
$xxxxx............................$xxxxx.............................$xxxxx

$xxxxx............................$xxxxx.............................$xxxxx

$xxxxx............................$xxxxx.............................$xxxxx

$xxxxx............................$xxxxx.............................$xxxxx


External file containing the data:
1.xls.................................2.xls..................................
3.xls


Now, say I type in refrence to 1.xls in colum Jan-2004,
='X:\[1.xls]Sheet1'!$A$1

Now, if I know that the 3 xls' files are exactly the same in layout,
would have to type in for Feb-2004; ='X:\[2.xls]Sheet1'!$A$1

and for Mar-2004; ='X:\[3.xls]Sheet1'!$A$1

Now you can imagine that If I have a period of 10 years, I would hav
to type in 120 file references manualy.

Is there any possibility to perform this with using a formula or kin
in Excel and force it to take a string from other cell and put that i
front of .xls and look that up??

I hope there is, or I will go crazy.

Ragards,
Zurv

 

[Don Guillett]

You can use the INDIRECT function with OPEN workbooks.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Dear all,

I have the following question;

Is it possible in Excel, when typing in your external path to your
external file, to use name references given in other cells (and thus
make it "dynamic")? So, let's say I have the following situation;

Uhave 3 colums, in which the data are meant for 3 different months;

Jan-2004.......................Feb-2004.........................Mar-2004
$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx


External file containing the data:
1.xls.................................2.xls.................................
...
3.xls


Now, say I type in refrence to 1.xls in colum Jan-2004,
='X:\[1.xls]Sheet1'!$A$1

Now, if I know that the 3 xls' files are exactly the same in layout, I
would have to type in for Feb-2004; ='X:\[2.xls]Sheet1'!$A$1

and for Mar-2004; ='X:\[3.xls]Sheet1'!$A$1

Now you can imagine that If I have a period of 10 years, I would have
to type in 120 file references manualy.

Is there any possibility to perform this with using a formula or kind
in Excel and force it to take a string from other cell and put that in
front of .xls and look that up??

I hope there is, or I will go crazy.

Ragards,
Zurvy

 

[Guest]

I have a simular problem and I cann't use INDIRECT because I have a weekly
file and I won't open over 100 files to let work the function. No other
ideas ?

You can use the INDIRECT function with OPEN workbooks.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Dear all,

I have the following question;

Is it possible in Excel, when typing in your external path to your
external file, to use name references given in other cells (and thus
make it "dynamic")? So, let's say I have the following situation;

Uhave 3 colums, in which the data are meant for 3 different months;

Jan-2004.......................Feb-2004.........................Mar-2004
$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx


External file containing the data:
1.xls.................................2.xls.................................
...
3.xls


Now, say I type in refrence to 1.xls in colum Jan-2004,
='X:\[1.xls]Sheet1'!$A$1

Now, if I know that the 3 xls' files are exactly the same in layout, I
would have to type in for Feb-2004; ='X:\[2.xls]Sheet1'!$A$1

and for Mar-2004; ='X:\[3.xls]Sheet1'!$A$1

Now you can imagine that If I have a period of 10 years, I would have
to type in 120 file references manualy.

Is there any possibility to perform this with using a formula or kind
in Excel and force it to take a string from other cell and put that in
front of .xls and look that up??

I hope there is, or I will go crazy.

Ragards,
Zurvy

 

[Dave Peterson]

Harlan Grove wrote a UDF that allows you to retrieve values from a closed
workbook when you build that string that points at the
drive/folder/filename/sheetname/range address.

http://www.google.com/[email protected]

I have a simular problem and I cann't use INDIRECT because I have a weekly
file and I won't open over 100 files to let work the function. No other
ideas ?

You can use the INDIRECT function with OPEN workbooks.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Dear all,

I have the following question;

Is it possible in Excel, when typing in your external path to your
external file, to use name references given in other cells (and thus
make it "dynamic")? So, let's say I have the following situation;

Uhave 3 colums, in which the data are meant for 3 different months;

Jan-2004.......................Feb-2004.........................Mar-2004
$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx$xxxxx............................$xxxxx.............................$xxxxxx


External file containing the data:
1.xls.................................2.xls.................................
...
3.xls


Now, say I type in refrence to 1.xls in colum Jan-2004,
='X:\[1.xls]Sheet1'!$A$1

Now, if I know that the 3 xls' files are exactly the same in layout, I
would have to type in for Feb-2004; ='X:\[2.xls]Sheet1'!$A$1

and for Mar-2004; ='X:\[3.xls]Sheet1'!$A$1

Now you can imagine that If I have a period of 10 years, I would have
to type in 120 file references manualy.

Is there any possibility to perform this with using a formula or kind
in Excel and force it to take a string from other cell and put that in
front of .xls and look that up??

I hope there is, or I will go crazy.

Ragards,
Zurvy