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/groups?threadm=O5aTScOyDHA.2084%40TK2MSFTNGP09....

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/groups?selm=sH1La.4495%24cJ5.436%40www.newsrang...

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

--
To top-post is human, to bottom-post and snip is sublime.