I have a complex model that uses range names and array formulas to
simplify the model. It uses multiple worksheets to do detail
calculations and another group of worksheets to summarize those
calculations. Think of the detail calculations as a module. In the
process the model is used for, multiple modules of data need to be
combined into the same summary. For perspective, the current model
contains 24 worksheets and 300 range names of which roughly 1/3
constitute an “expansion” module. The number of these modules varies,
so I need the ability to "add-on" expansion modules as required. I
would like to build a "base" model that contains the fewest modules
possible and another file that contains the worksheets to add to the
"base" to expand it.

The problem, copying the add-on sheets into the base model requires
that the range names used be unique. Otherwise during the copy
process, the references in the copied sheets get pointed to the range
names that exist in the "base" file. I have been using sheet specific
range name definitions to avoid having to create unique range names;
I.E. 'sheet1module1'!samerangename = 'sheet1module1'!A1:Z1 and
'sheet1module2'!samerangename = 'sheet1module2'!A1:Z1. Consider, each
add-on would have to either A) have all the module specific range names
altered along with all the formulas referencing these range names OR B)
be a separate "add-on" file created for each possible module addition,
thus limiting the number of module additions. Neither is acceptable
based on the project definition.

Without relying on external file references, how do I merge two files
and maintain the integrity of the range name definitions on the
"imported" file?


--
FlaAl
------------------------------------------------------------------------
FlaAl's Profile: http://www.excelforum.com/member.php...o&userid=34753
View this thread: http://www.excelforum.com/showthread...hreadid=545153