Create a drop-down list from a range of cells

[weswhite7]

I'm trying to create a list in a workbook from a range of cells in a
different worksheet in a different workbook. I have worked through Excels
guide on how to create a drop-down list from a range of cells, however, when
I follow the steps exactly and then go through the validation, it tells me
"You may not use references to other worksheets or workbooks for Data
Validation criteria." Yet, these are instructions that are provided by
Microsoft Excel on how to create a drop-down list from an external workbook.

Any help here? If you search in the box for create list and then select the
"Create a drop-down list from a range of cells" instruction that is what I
have been using.

Any help would be appreciated. Thanks.

 

[Bassman62]

Data validation cannot use a reference to another workbook for criteria
(directly).
Here's a work-around;
If you want validation in say "Book1" and your validation list is in "Book2"
Somewhere in Book1, use linking formulae to return the "list" from Book2.
Such as =[Book2]Sheet1!A1
From this point you can follow the insturctions. Be sure to use a defined
name if the reference is on a different sheet.

Regards,
Dave

 

[Gord Dibben]

See Debra Dalgleish's site for using another workbook as the source for a DV
list.

NOTE..........the source WB has to be open.


Gord Dibben MS Excel MVP