Best approach to this problem

P

PhaseDragon

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

Tom Ogilvy

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

Frank Stone

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