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?

use dynamic defined names

Name: List1
Refersto: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1:$A:$A),1)

or setup your list in a database and use a querytable in your sheets to
retrieve the data.


--
Regards,
Tom Ogilvy


" wrote in
message ...
Another related question: When opening a file that has named ranges in
Excel97 the program crashes if I answer Yes to Update External Data when
the
spreadsheet opens. Is there a solution to this other than opening the
Codes
file first?

" wrote:

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?

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?
.