how to reference data in different xls files

[SteveVine]

Hi group

I have a sheet I call Consolidated.Xls
Cells on the Consolidated.Xls sheet get data from various other files.
For example:

cell B2 contains ....
='C:\1Sv\DATA\840\Weekly SpreadSheets\[14-06-04.xls]Weekly Sheet'!A8
cell C2 contains ....
='C:\1Sv\DATA\840\Weekly SpreadSheets\[21-06-04.xls]Weekly Sheet'!A8
cell D2 contains ....
='C:\1Sv\DATA\840\Weekly SpreadSheets\[28-06-04.xls]Weekly Sheet'!A8

My question is this.
Can I replace the filename (i.e. [28-06-04.xls]) with a pointer to a cell on
the Consolidated.Xls that actually contains the filename.
i.e. can I insert something like A1 instead of [28-06-04.xls], where cell A1
contains the filename 28-06-04.xls ?

I hope you can help.

Thanks

Steven Vine

 

[Andy Brown]

i.e. can I insert something like A1 instead of [28-06-04.xls], where cell
A1

contains the filename 28-06-04.xls ?

The INDIRECT function does this kind of thing. However, I believe it won't
work with closed wbs.

Have a look at downloading MoreFunc.XLL from http://longre.free.fr/english/
("INDIRECT.EXT ; same as INDIRECT working also with closed workbooks").

HTH,
Andy

 

[SteveVine]

Thanks Andy

I did not know about Indirect.
I've just download morefunc and will try that now.

I may be back

Thanks very much for the speedy reply.

Regards

Steven Vine

i.e. can I insert something like A1 instead of [28-06-04.xls], where

cell

A1
contains the filename 28-06-04.xls ?

The INDIRECT function does this kind of thing. However, I believe it won't
work with closed wbs.

Have a look at downloading MoreFunc.XLL from http://longre.free.fr/english/
("INDIRECT.EXT ; same as INDIRECT working also with closed workbooks").

HTH,
Andy