Dynamic file referencing?
 

ok, I'm builind a master file that draws figures from many other files
in series by month with a naming convention of mm-yyyy-SiteName.xls. My
problem lies in that if I have a static reference in my cells, I can
get and store the data no problem, but since expansability is an issue,
I was going to reference a column containing the SiteName portion of
the filenames and reference the needed data with the indirect function.

The problem is that Indirect doesn't work if the file in it's reference
is NOT open, returning #REF! instead. Due to the sheer number of files
being worked with, this is impractical for the slower machines in use
here. (so VB automated opening of needed files is out of the question)

Is there some other way I can bypass this? Is there a property, in
addition to .Value I can set on cells that will get saved when the
master workbook is saved? (so I can write something similar to
indirect, but works without open files?) or is there some other way?
please help!

Dynamic file referencing?
 

You may use the INDIRECT.EXT function, that works on closed documents. You
can find reference he
http://xcell05.free.fr/english/moref...direct.ext.htm
You need to download it before being able to use it.

Hope this helps,
Miguel.

" wrote:

ok, I'm builind a master file that draws figures from many other files
in series by month with a naming convention of mm-yyyy-SiteName.xls. My
problem lies in that if I have a static reference in my cells, I can
get and store the data no problem, but since expansability is an issue,
I was going to reference a column containing the SiteName portion of
the filenames and reference the needed data with the indirect function.

The problem is that Indirect doesn't work if the file in it's reference
is NOT open, returning #REF! instead. Due to the sheer number of files
being worked with, this is impractical for the slower machines in use
here. (so VB automated opening of needed files is out of the question)

Is there some other way I can bypass this? Is there a property, in
addition to .Value I can set on cells that will get saved when the
master workbook is saved? (so I can write something similar to
indirect, but works without open files?) or is there some other way?
please help!