Indirect Function with Closed files

[Jerri]

Problem: I'm needing to pull data from a particular cell
from several files, (could be 50 or more), without opening
all the files. Since I will be labeling my file names with
numbers like 100.xls, 101.xls, 102.xls... I have a
concatenating function built that will create a string
telling excel where to go get the answer. This way I do
not have to type the file names of all the files. Now I
just need excel to perform the directive. And so far
INDIRECT is the only thing that I have found that will
carry out the instruction but the problem is the file must
be open. Are there any work arounds for my problem?

 

[jaf]

Hi Jerri,
Using indirect, no.
You can use a sheet to build the formulas for your links.

C D E F G H I
drive path file ext sheetname cell formula
c:\ My Documents 100 .xls sheet1 $A$1 ='c:\My
Documents\[100.xls]sheet1'!$A$1
Using this formula "='" & C17 &D17 & "\["&E17&F17&"]"&G17&"'!"&H17



Now you can use column E to automatically generate the file names.
(target cells also)
You have to copy & PasteSpecial, values, then F2 enter.

Or use VBA.