Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Parameterize Workbook Links?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Parameterize Workbook Links?
try
=indirect($B$2&"$A$1) "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Parameterize Workbook Links?
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) "bj" wrote: try =indirect($B$2&"$A$1) "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Parameterize Workbook Links?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Parameterize Workbook Links?
you are correct, it is the same for 2003
"Jon" wrote: 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) "bj" wrote: try =indirect($B$2&"$A$1) "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Parameterize Workbook Links?
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Parameterize Workbook Links?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - #REF! in Links | Excel Worksheet Functions | |||
Links in Excel 2003 | Excel Discussion (Misc queries) | |||
Links - Excel 2003 | Excel Discussion (Misc queries) | |||
Dynamic Links Excel 2003 | Excel Discussion (Misc queries) | |||
not updating links in excel 2003 | Excel Worksheet Functions |