Making a file name in a formula a variable

[Sumit Khanna]

Hi. I have about a 100 files from which I need to extract
some data. The row/cell where the data is located is in
the identical location in each of these file.

I have setup a new file and created a simple formulae that
links to the data cell in one of the files. One of my
options is that I copy the formulae down to another 99
rows and change the name in each of those formulae. The
names of the 100 files are sequential by date (eg. 2-25-
2004, 2-26-2004, 2-27-2004 and so on...)

I was hoping to create a new col. in the destination file
with all the 100 file names (its easy since I can just
drag the formulae down). The formulae that pulls the data
from the source file looks like:

=+'2-25-2004.xls'!$B$9

Since the only part of the above formulae that changes is
the file name, I was hoping I could link that piece to the
new col. with the file names.

Any suggestions would be greatly appreciated.

Thanks in advance.

Regards,
Sumit Khanna

 

[Frank Kabel]

Hi
as INDIRECT works only with open workbooks see the following
thread for accessing closed workbooks (in your example INDIRECT.EXT
from the add-in morefunc.xll should work):
http://tinyurl.com/2c62u