Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter variable data into a formula | Excel Discussion (Misc queries) | |||
Cell variable in formula | Excel Worksheet Functions | |||
Aling multiple sets of data by header column | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |