CK

OP wanted to have a list source from a closed workbook.

Cannot be done.

Quoted from Debra Dalgleish's site.................................

You can use a list from another workbook as the source for a Data Validation
dropdown list.

For data validation to work, the workbook which contains the list must be
open, in the same instance of Excel. You could create the list in a workbook
that is always open, but hidden, such as the Personal.xls workbook.

End quote............................

Also from Excel Help on trouble-shooting DV

Are the referenced cells on the same worksheet or a different workbook?
Cell references have to be to cells on the same worksheet. If you have to
refer to a different worksheet or a different workbook, use a defined name
and make sure the workbook is already open.



Gord Dibben MS Excel MVP


On Wed, 22 Apr 2009 13:19:02 -0700, CK wrote:

Funny, I just learned this today again.

1) First make your list in the other workbook.

2) Highlight the list you want to show in the drop down

3) In the upper right corner, exactly above the Column A title, you'll see a
the cell row/column of the first cell you highlighted. click on that box,
then type a title or name (such as "Menu" - don't need quotes though). PRESS
ENTER!!

Then

4) Go to workbook you want drop down menu to appear on; click on a open cell

5) Open VALIDATION, in the DATA menu

6) In ALLOW: click on what you want to see. If you have a list of options
such as: good, bad, ugly... Then you click on LIST. Make sure both boxes are
checked - Ignore blank and In-cell dropdown.

7) Then in SOURCE: type this =Menu YOU MUST ADD THE EQUAL SIGN for this to
work from another notebook.

8) Click Okay; You can cut and past this new pull down cell anywhere else
in your workbook and the pull down will still work properly.


Hope this helps,

"katiapro93" wrote:

Hi, can anyone help

Is there anyway I can create a drop down list on a form using data that is
located in another workbook yet not have to open it everytime I want to use
the form.

Maybe there is a function that does the same thing? i.e. gets data from a
column and makes a list so you can click a selection.