Control of named ranges

  • Thread starter Thread starter David
  • Start date Start date
D

David

Here's the scenario:
I have a small number of lookup tables that feed a handful
of excel workbooks. Up until now I have had a duplicate
set of named ranges in each workbook which are (or should
be !) an exact copy of the master set.

The workbooks may contain up to 60 000 data rows and each
row may use several lookup functions referring to the
aforesaid named ranges. This works quite well but there is
the problem of controlling the periodic updating of all of
the named ranges.

I have tried referring out to the file containing the
master set of names. Whilst this solves the control
problem, unfortunately it takes an inordinate amount of
time to run the processes in the workbooks due to the time
it takes for the links to update (with the master file
closed).

I could have the user open the master file to speed things
up (I would prefer not to).

....Alternatively I am wondering if there is a neat way of
linking local ranges within each file to the closed master
file (not long to update) then lookup the local ranges.
However, if the master file ranges were extended then the
local ranges would have to automatically extend also. Is
there a way of doing this without using a macro?

Thanks in anticipation,
David
 
If you have this large number of records, you should definitely go for
macro. You may even need Access if you want the job done faster.
 
Perhaps using a defined named range would help.
goto insert>name>define
type in the name
in the "refers to" box use the offset formula (look in HELP)

=offset($a$3,0,0,counta($a:$a),4)
as written it will define $A$3:$d$whatever
will automatically adjust the named range for addition or deletion of rows
by studying the offset function, you can adapt this to many situations.
 
Back
Top