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?