Excel 2003 - Parameterize Workbook Links?

[Guest]

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]

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.

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

 

[Guest]

you are correct, it is the same for 2003

DHL,

I tried using indirect to solve this for one of my spreadsheets, but it only
worked if the second file was open. If it was not open, the data didn't get
pulled in. (I'm using Excel 2000... not sure if 2003 is different)

try
=indirect($B$2&"$A$1)

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

 

[Guest]

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.

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.

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]

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.

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.

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.

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