Problems with external references when creating a drop down list
 

I have a large spreadsheet with several large drop down lists. The lists are
all in a separate workbook so I have defined a name with an external
reference to the lists. The problem is that the drop down lists will only
work when both spreadsheets are open but I don't want to open the spreadsheet
containing the lists everytime as it is a really large workbook. How can I
get the drop down list to work with just the workbook open where I have
validated the cells?
Thanks.

Write 2 macros..
in the workbook that is the 'source' of the dropdowns, have it export the
list to a general filename "myDropdown.txt". Make this macro run every time
the workbook is closed, that way the most up to date list will be there.

In the workbook that uses the list, write a macro that will refresh the list
as needed (probably on opening) from the text file.

I know it doesn't seem elegant, but it will work and eliminate the extra
overhead of linking workbooks.

"andreah" wrote:

I have a large spreadsheet with several large drop down lists. The lists are
all in a separate workbook so I have defined a name with an external
reference to the lists. The problem is that the drop down lists will only
work when both spreadsheets are open but I don't want to open the spreadsheet
containing the lists everytime as it is a really large workbook. How can I
get the drop down list to work with just the workbook open where I have
validated the cells?
Thanks.