Indirect Function with Closed files

  • Thread starter Thread starter Jerri
  • Start date Start date
J

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?
 
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.
 
Back
Top