setting constant path

[Jayen]

Help please.
I have one workbook which is a csv file lets call it source1.xls from a
banking system. I also have 28 workbooks (reports) which derive data from
the csv file. Some of the cells in the 28 books do calculations based on
values from source1.xls. Is there a way that I can specify, in an add in or
macro or whatever, a constant path for the file so that when cell A2 of a
report looks at a cell in the source, it doesn't have lets say path like
=SUMPRODUCT(--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$DK$1:$DK$65536="230"),
--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$D$1:$D$65536="973"),
--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$DL$1:$DL$65536="024"),
'T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$CB$1:$CB$65536)--

This is rather long and if the source file changes location it becomes
difficult to change every cell in the reports that references the source.
Would prefer just to refer to a path variable just dont know how to do it.
Thank you.
Jayen

 

[Don Guillett]

In your workbook>insert>name>define>name it something like source
In the refers to box type in something like
='C:\myfoldernamehere\[filename.xls]sheetname'!$D$7:$D$21
then in your formula just =sum(source)
Change the defined name as desired.

 

[Steve]

In your workbook>insert>name>define>name it something like source
In the refers to box type in something like
='C:\myfoldernamehere\[filename.xls]sheetname'!$D$7:$D$21
then in your formula just =sum(source)
Change the defined name as desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

Help please.
I have one workbook which is a csv file lets call it source1.xls from a
banking system.  I also have 28 workbooks (reports) which derive datafrom
the csv file. Some of the cells in the 28 books do calculations based on
values from source1.xls.  Is there a way that I can specify, in an add in
or
macro or whatever,  a constant path for the file so that when cell A2of a
report looks at a cell in the source, it doesn't have lets say path like
=SUMPRODUCT(--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$DK$1:$DK$65536="230"),
--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$D$1:$D$65536="973"),
--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$DL$1:$DL$65536="024"),
'T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$CB$1:$CB$65536)--

This is rather long and if the source file changes location it becomes
difficult to change every cell in the reports that references the source.
Would prefer just to refer to a path variable just dont know how to do it.
Thank you.
Jayen

Couldn't you just create a constant string and set it to the path?

Const file_path as String
file_path = "T:\Operational\BQC\BNA_Reports\BNA\Source files\"

 

[Jayen]

Thank you, am trying your suggestion now.

 

[Jayen]

Don,

I am using excel 2007 and under the insert menu, there is no option for
defining name as you suggested.

 

[Don Guillett]

Formulas>Define name

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don,

I am using excel 2007 and under the insert menu, there is no option for
defining name as you suggested.
--
Jayen

Help please.
I have one workbook which is a csv file lets call it source1.xls from a
banking system. I also have 28 workbooks (reports) which derive data
from
the csv file. Some of the cells in the 28 books do calculations based on
values from source1.xls. Is there a way that I can specify, in an add in
or
macro or whatever, a constant path for the file so that when cell A2 of
a
report looks at a cell in the source, it doesn't have lets say path like
=SUMPRODUCT(--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$DK$1:$DK$65536="230"),
--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$D$1:$D$65536="973"),
--('T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$DL$1:$DL$65536="024"),
'T:\Operational\BQC\BNA_Reports\BNA\Source
files\[fdbpre.xls]fdbpre'!$CB$1:$CB$65536)--

This is rather long and if the source file changes location it becomes
difficult to change every cell in the reports that references the source.
Would prefer just to refer to a path variable just dont know how to do
it.
Thank you.
Jayen