setting constant path
 

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

setting constant path
 

In your workbookinsertnamedefinename 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

"Jayen" wrote in message
...
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

setting constant path
 

On Nov 14, 9:52*am, "Don Guillett" wrote:
In your workbookinsertnamedefinename 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
"Jayen" wrote in message

...

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

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\"

setting constant path
 

Thank you, am trying your suggestion now.
--
Jayen


"Jayen" wrote:

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

setting constant path
 

Don,

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


"Jayen" wrote:

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

setting constant path
 

FormulasDefine name

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jayen" wrote in message
...
Don,

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


"Jayen" wrote:

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