Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
References and named ranges
I am working with a project that consist of multiple workbooks, one wb
is the main program, the second wb is an inventory of items and the remaining are calculation workbooks. The main program opens the inventory and calculations sheets based on user actions. The inventory workbook, "c:\Inventory 050107.xls", contains named ranges with inventory of items and their price. the name property for this file is "Company_x_Inventory". for example there is a named range rngBoltTypes and rngBoltPrices. these ranges are dynamic and will change in count. the calculation workbook will need to lookup the price of a bolt based on the type. the code and spreadsheet refers to named ranges in the inventory workbook. For example, the calculation workbook has the following in a cell formula: =LOOKUP($a$1,'c:\Inventory 050107.xls'!rngBoltTypes,'c:\Inventory 050107.xls'!rngBoltPrices) I added the inventory workbook as a reference in the calculation workbook, to allow the use of the named ranges. So far everything works well. The issue that I am encountering is the client wants to be able to store and use historical inventory files. For example. there is an inventory file "c:\Inventory 020107.xls" and "c:\Inventory 050107.xls". Both contain the same range names and structure. Through code, the variable strInventoryToUse is set to the appropriate inventory file path & name Through the code in the main program, the reference to the inventory in VBA will added during run time ActiveWorkbook.VBProject.References.AddFromFile strInventoryToUse and removed with the BeforeClose event ActiveWorkbook.VBProject.References.Remove strInventoryToUse First Question. is changing the reference a save practice? what is the risk? Second question and more important. even though the reference is changed, the lookup formula still is looking at 'c:\Inventory 050107.xls'!rngBoltTypes. Is there a way to refer to the named ranges in the referenced file instead of the file name? For example: =LOOKUP($a$1,'Company_x_Inventory'! rngBoltTypes,'Company_x_Inventory'!rngBoltPrices) Or automatically update the links from the old inventory file to the file specified by the code? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to replace cell references with Named ranges | Excel Discussion (Misc queries) | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
dynamically building references to named ranges | Excel Discussion (Misc queries) | |||
Named ranges: don't want absolute references | Excel Discussion (Misc queries) | |||
Named ranges: don't want absolute references | Excel Discussion (Misc queries) |