Using a variable to represent the pathname in a formula

[David Jones]

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

 

[Dallman Ross]

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-

 

[David Jones]

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.

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-

 

[Dave Peterson]

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.

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

 

[David Jones]

Thanks,

I'll have a look at Harlan's work

Regards

Dave

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.

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

 

[David Jones]

Bingo!

Exactly what I needed from Laurent Longre

Many thanks

Dave

Thanks,

I'll have a look at Harlan's work

Regards

Dave

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.

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