Hi
works for me without a problem but find below the repost from 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