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?