To do this, the user would have to enter in a code, which relates to
the excel filename.
I can build up the formula needed using vba code assigned to a button,
but this seems very "clunky" and prone to user error. Surely there must
be a simple existing function to do this.
Everything I search for appears to require the workbooks to be open,
which in this case is unsuitable.
Failing that, if I have to stick with VBA code, can code be assigned to
cells? I'm more used to VB then VBA.
Using Excel 2000.
Thanks in Advance.
---
Message posted from http://www.ExcelForum.com/
Frank
Hi
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
Frank
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/groups?threadm=O5aTScOyDHA.2084%40TK2MSFTNGP09.phx.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/groups?selm=sH1La.4495%24cJ5.436%40www.newsranger.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
--
To top-post is human, to bottom-post and snip is sublime.
> 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.
Thanks for the info.
> 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.
No, definetly no aversion! I like this solution but in most
circumstances it is not required. So if INDIRECT.EXT is not an option
the 4th solution would be my suggestion :-)
Though I will add this to my 'standard template' for answering INDIRECT
+ closed workbook related questions to ensure complete answers ;-)
Regards
Frank
Begging the question whether most circumstances involve dereferencing a single,
unnamed cells rather than either multiple cell ranges or named, single area
ranges.
=INDIRECT.EXT("'C:\somedir\["&B2&".xls]Summary Sheet'!$E$5")