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