Hi Jay
unfortunately you can't do this - it won't work. Find below a repost
from the discussion mentioned in my previous post:
-----------------Author: Harlan Grove:
are you using INDIRECT.EXT in combination with other formulas (e.g.
VLOOKUP) or stand-alone. The latter one will work. Please post your
complete formula to give you some advise
It's not just when needing to return multiple cell ranges that
INDIRECT.EXT
breaks down. It also doesn't dereference defined names, even those
referring to
single cells, and there are some systems on which it just doesn't work.
Look
through the archives from about a year ago, and you'll see a few
threads in
which Laurent Longre and I discussed this problem.
For the OP, here's the complete list of alternatives, now updated to
include
SQL.REQUEST. Frank has a peculiar aversion to the 4th one. Guess he
doesn't want
to give complete answers.
1. Use formulas to create literal external reference formulas as text,
e.g.,
="=SUMPRODUCT(('C:\somedir\"&SubDir&"\["&Filename&"]"&WorksheetName&"'!
"&
FirstRangeAddress&"="&whatever&")*'C:\somedir\"&SubDir&"\["&Filename&"]
"&
WorksheetName&"'!"&SecondRangeAddress&")"
To convert these to formulas, copy the cells containing them and
paste-special
as Values on top of themselves, then Edit > Replace, finding = and
replacing
with = . Yes, replace the equal signs with themselves. This effectively
enters
all of these as formulas. This is the most effective technique using
only
built-in functionality, but if you change the subdirectory or base file
names,
you'll need to repeat this procedure. Also, it may require a LOT of
memory.
2. Try Laurent Longre's MOREFUNC.XLL add-in, which is avaialable at
http://longre.free.fr/english/
It provides a function called INDIRECT.EXT which would do what you
want. It
works on some of the PCs I use, but not on one running Windows Me.
Also, it
doesn't work with defined names in closed workbooks. You'd use it like
=SUMPRODUCT((INDIRECT.EXT("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&FirstRangeAddress)=whatever)
*INDIRECT.EXT("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&SecondRangeAddress))
3. Slow. Use SQL.REQUEST *if* the data you'd access looks like a
database table,
i.e., it's in a single area, multiple cell *named* range with field
names in the
top row. See the following linked thread for more details.
http://www.google.com/[email protected]
x.gbl
4. Also slow, but it seems to be more robust than #2 and, unlike #3,
imposes no
restrictions on data layout - a udf which uses a separate Excel
application
instance to pull data from closed workbooks. The VBA code is in the
linked
article.
http://www.google.com/[email protected]
..com
Use it like
=SUMPRODUCT((pull("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&FirstRangeAddress)=whatever)
*pull("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&SecondRangeAddress))
Similar to #1, there's also John Walkenbach's GetValue function, but
it's only
useful when called by macros. But for completeness,
http://j-walk.com/ss/excel/tips/tip82.htm
--
Regards
Frank Kabel
Frankfurt, Germany
Hi Frank, you're right - the other files are closed. Unfortunately,
the firewall put in place by the company does not allow me to use the
URL you provided. I was wondering if I can modify the VLOOKUP
formula incorporating something like
03/04/04 =CONCATENATE(MID(TEXT(A1,"mm
dd"),1,2),"_",MID(TEXT(B3,"mm dd"),4,2))
This seems to be working by itself. But I am doubtful if VLOOKUP can
accomodate a concatenated label.
Jay
----- Frank Kabel wrote: -----
Hi Jay
I assume that the other file is NOT opened (if it is opened,
INDIRECT is the function of choice for you - but this function
won't work with closed workbooks).
Have a look at the following thread for some other alternatives
(though INDIRECT.EXT probably won't work either in your case as
you need to return an array as second parameter of your VLOOKUP
function):
http://tinyurl.com/2c62u
--
Regards
Frank Kabel
Frankfurt, Germany
I have a master data file that uses VLOOKUP to pull over daily
price > on hundreds of products and then calculates the
year-to-date average > thereof. The daily price is captured on
a daily basis and archived > as such - i.e. one file for each
day. The syntax of the VLOOKUP > contains the path linked to the
folder where daily files are > archived. The file name
convention of the daily files is "mm_dd.xls" >> Date
Price > 03/04/2004
=VLOOKUP(A2,'x:\2004\03\[03_04.XLS]DAILY >
FILE'!$A$1:$Q100,12,FALSE) >> I was wondering if there is a way
of linking these two elements > whereby if I change the date to
03/05/2004, could the formula change > itself to
=VLOOKUP(A2,'x:\2004\03\[03_05.XLS]DAILY >
FILE'!$A$1:$Q100,12,FALSE) dynanmically? >> Thanks.
