Sound like a good attack plan.
I might offer 1 suggestion to your delima.
instead of adjusting your range name each time, have the
range 2 to 3 time (or larger) the size the range needs to
be to cover all your codes. this way you wont have to be
re-seting the range all the time.

-----Original Message-----
I was wondering if anyone has a suggestion as to the best
solution to this
application.

I have several spreadsheets tied together. One sheet is
the Codes and is
used for lookups in other files. The lookup formulas use
this file with a
named range which defines the list of codes.

When I want to add a new Code I have to change the named
range to reflect
the added Code in every file that uses the lookup.

Also, I copy some of the Codes to a hidden portion of
some spreadsheets
which I use for data validation lists in some cells to
ensure there are no
misspelled codes. These also have to be updated when a
new code is entered.

My current idea was to record a macro with an Excel Query
to get the partial
list in the hidden portion of the list which is called
with the
ThisWorkbook.Workbook_Open. The macro will also build the
validation list and
adjust the named range.

Is there a better solution?
.