Using a variable to represent the pathname in a formula
Hi,
I have a number of "child" spreadsheets that rely on a master spreadsheet for their input. Calculations and charting are performed in the "child" spreadsheets for different departments. I need to refer to the master spreadsheet using a variable. For example, if the master spreadsheet is located at \\server\share\folder1\folder2, I need to be able to set this location to a variable, call it location, and then in my "child" spreadsheets be able to enter something like ='location\[spreadsheet.xls]Sheet1'!$A$1 or perform more complex calculations. Can this be done? thanks Dave |
Using a variable to represent the pathname in a formula
In , David Jones
spake thusly: I need to refer to the master spreadsheet using a variable. For example, if the master spreadsheet is located at \\server\share\folder1\folder2, I need to be able to set this location to a variable, call it location, and then in my "child" spreadsheets be able to enter something like ='location\[spreadsheet.xls]Sheet1'!$A$1 Can't you just map the path to a drive letter in your operating system (which seems to be Windows)? From the Windows Explorer with the directory highlighted, click on the Tools menu and and select "Map Network Drive". -dman- |
Using a variable to represent the pathname in a formula
Yes that is an option, however I would prefer using a UNC address as I will
be maintaining both a live and development copy of the spreadsheets and would prefer to simply change a variable rather than remapping a drive. thanks for your comments. "Dallman Ross" <dman@localhost. wrote in message ... In , David Jones spake thusly: I need to refer to the master spreadsheet using a variable. For example, if the master spreadsheet is located at \\server\share\folder1\folder2, I need to be able to set this location to a variable, call it location, and then in my "child" spreadsheets be able to enter something like ='location\[spreadsheet.xls]Sheet1'!$A$1 Can't you just map the path to a drive letter in your operating system (which seems to be Windows)? From the Windows Explorer with the directory highlighted, click on the Tools menu and and select "Map Network Drive". -dman- |
Using a variable to represent the pathname in a formula
You'd want to use =indirect() to build that string/formula.
But =indirect() won't work when the sending workbook is closed. You may want to consider just changing the link via Edit|links. But if you want to try... Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. David Jones wrote: Hi, I have a number of "child" spreadsheets that rely on a master spreadsheet for their input. Calculations and charting are performed in the "child" spreadsheets for different departments. I need to refer to the master spreadsheet using a variable. For example, if the master spreadsheet is located at \\server\share\folder1\folder2, I need to be able to set this location to a variable, call it location, and then in my "child" spreadsheets be able to enter something like ='location\[spreadsheet.xls]Sheet1'!$A$1 or perform more complex calculations. Can this be done? thanks Dave -- Dave Peterson |
Using a variable to represent the pathname in a formula
Thanks,
I'll have a look at Harlan's work Regards Dave "Dave Peterson" wrote in message ... You'd want to use =indirect() to build that string/formula. But =indirect() won't work when the sending workbook is closed. You may want to consider just changing the link via Edit|links. But if you want to try... Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. David Jones wrote: Hi, I have a number of "child" spreadsheets that rely on a master spreadsheet for their input. Calculations and charting are performed in the "child" spreadsheets for different departments. I need to refer to the master spreadsheet using a variable. For example, if the master spreadsheet is located at \\server\share\folder1\folder2, I need to be able to set this location to a variable, call it location, and then in my "child" spreadsheets be able to enter something like ='location\[spreadsheet.xls]Sheet1'!$A$1 or perform more complex calculations. Can this be done? thanks Dave -- Dave Peterson |
Using a variable to represent the pathname in a formula
Bingo!
Exactly what I needed from Laurent Longre Many thanks Dave "David Jones" wrote in message ... Thanks, I'll have a look at Harlan's work Regards Dave "Dave Peterson" wrote in message ... You'd want to use =indirect() to build that string/formula. But =indirect() won't work when the sending workbook is closed. You may want to consider just changing the link via Edit|links. But if you want to try... Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can find the function at Harlan's FTP site: ftp://members.aol.com/hrlngrv/ Look for pull.zip Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ That includes =indirect.ext() that may help you. David Jones wrote: Hi, I have a number of "child" spreadsheets that rely on a master spreadsheet for their input. Calculations and charting are performed in the "child" spreadsheets for different departments. I need to refer to the master spreadsheet using a variable. For example, if the master spreadsheet is located at \\server\share\folder1\folder2, I need to be able to set this location to a variable, call it location, and then in my "child" spreadsheets be able to enter something like ='location\[spreadsheet.xls]Sheet1'!$A$1 or perform more complex calculations. Can this be done? thanks Dave -- Dave Peterson |
All times are GMT +1. The time now is 02:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com