Data Validation from list on separate Workbook

[Guest]

I am trying to make a dropdown box from a list of customers that are
contained in a database in a separate workbook. Is there a way I can use
Data Validation and have the source come from an entirely separate workbook?
Thanks

 

[Debra Dalgleish]

You can use a list from another open workbook, as described here:

http://www.contextures.com/xlDataVal05.html

 

[Guest]

Yes you can, but generally the list will only work while you have the other
workbook open. Debra Dalgleish has some good information at
www.contextures.com on it: http://www.contextures.com/xlDataVal05.html

Probably a better, more 'robust' way that will work all the time is to set
up a worksheet that you will eventually hide from view. On that sheet, set
up an area with 1-to-1 links to the list in the other workbook. They don't
have to be in same cells, just have to have individual references. Example:
in the other workbook maybe your list is on Sheet1 and goes from A1:A10. You
could put a formula like this into B1 (or C99 or where ever)
=[SourceListBook.xls]Sheet1!$A1
and then fill it down to include all entries in the othe book. Now use this
copy of the list as your source for the Data Validation. The 1-to-1 links
will always update, even with the other workbook closed as long as your
system knows where that workbook is.