| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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\" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
setting constant path
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What happened to setting up a constant using F4? | Excel Worksheet Functions | |||
| Setting a path to a folder | Excel Discussion (Misc queries) | |||
| setting transferred text as constant | Excel Discussion (Misc queries) | |||
| Setting a Constant when creating a new file from a template? | Excel Programming | |||
| filename and path as constant | Excel Programming | |||
Linear Mode
