Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to replace cell references with Named ranges lhim2 Excel Discussion (Misc queries) 0 January 28th 08 05:13 PM
Copy data in named ranges to a newer version of the same template to identical ranges handstand Excel Programming 0 August 21st 06 03:51 PM
dynamically building references to named ranges [email protected] Excel Discussion (Misc queries) 1 January 3rd 06 11:23 PM
Named ranges: don't want absolute references sonicblue Excel Discussion (Misc queries) 0 November 22nd 05 03:05 PM
Named ranges: don't want absolute references sonicblue Excel Discussion (Misc queries) 0 November 22nd 05 03:05 PM


All times are GMT +1. The time now is 12:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"