Maybe you could create a macro that would build the formulas that point at the
closed workbooks.

But that kind of thing would depend on where the formulas are, how many and
how/when those cells/formulas needed to be updated. And I'm sure it would have
some sort of impact on performance.


DHL wrote:

Thanks for the help on this. I like the addin =indirect.ext(), however, sadly
I need to use links in each cell of a huge array in my spreadsheet and
performance is unacceptably slow. Silly really because when I hard-code the
location of a file in each cell of that array, the performance is pretty good
but with =indirect.ext() the performance bombs!
Any further thoughts would be appreciated.

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

DHL wrote:

I have a link from one workbook to another e.g.
='C:\temp\[Book1.xls]Sheet1'!$A$1
However, I want to parameterize which external workbook to go to, e.g.
in my main workbook I would like to have a cell which contains the location
and name of another workbook and then use the contents of this cell to
dynamically make up my link query.
For example, say I have in cell $B$2 of my main workbook a value of
C:\temp\[Book1.xls]Sheet1
then in cell $B$3 I want to dynamically make a link to this external
workbook, e.g.
=<$B$2!$A$1
This would bring back the contents of cell $A$1 from
C:\temp\[Book1.xls]Sheet1, however, if I then changed the file & location
details in cell $B$2 to, for example, C:\windows\[Workbook.xls]Sheet2, the
dynamic link in cell $B$3 would then bring back the contents of cell $A$1
form C:\windows\[Workbook.xls]Sheet2.

Hope that makes sense. How would I do this?
Many thanks
DHL

--

Dave Peterson


--

Dave Peterson