validation drop down from closed workbook

K

karambos

I would like to have a drop down menu in a workbook that allows only
values form another workbook that may or may not be closed at the
time.

Is this possible and if so I'd be grateful for any hints, tips and
pointers in the right direction.

Thanks as always
 
D

Dave Peterson

This might work.

I created a new worksheet with formulas that refered back to the sometimes
closed workbook:

in A1:A10

='C:\my documents\excel\[book1.xls]Sheet1'!A1
thru
='C:\my documents\excel\[book1.xls]Sheet1'!A10

Then I named that range (Insert|name|define). I called it myList. And then I
hid that worksheet.

Then I use data|validation for the cell I wanted and used:
Allow: List
source: =myList

It seemed to work ok.
 
D

Debra Dalgleish

You could use Data>Get External Data> New Database Query to pull the
values into your workbook, and base the data validation list on the
query results.
 
Top