Debra Dalgleish has some instructions, but read the warnings!

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

redsunjvd wrote:

I am trying to create a drop down list for a cell where the data is in
another workbook. I followed directions from the on line help for Create a
drop-down list: I defined a name in the INSERT/NAME/DEFINE prompts. Then I
went to the VALIDATION/Settings prompts. when I enter the name I created in
INSERT/NAME/DEFINE section, click add, then close it comes back and tells me
I cannot use references to other worksheets or workbooks. --- Help, what am I
doing wrong?

--

Dave Peterson