create drop down list from different workbook

[Guest]

i followed the directions to create a drop down list on one workbook with
criteria from another workbook with data validation (as the directions stated
from the help section"Create a drop-down list from a range of cells "). when
i enter the name of the reference (=ValidDepts), i get an error message that
says, "you may not use references to other worksheets or workbooks for data
validation criteria." can someone help me with this?

Much Thanks

 

[Gord Dibben]

Brett

Check out the steps Debra Dalgleish outline here.

http://www.contextures.on.ca/xlDataVal05.html


Gord Dibben MS Excel MVP

 

[RagDyer]

You should realize though, that the "other" WB *must* be open for the DV
list to work.

If you still want to try this, you must assign a name to the range in the
source WB that the target WB can recognize.

To do this, open *both* WBs.
In the 'target' WB,
<Insert> <Name> <Define>

In the "Names In Workbook" box, enter:
ValidDepts

Select the *entire* contents of the "Refers To" box (turns black),
And navigate to the source WB, and select the range of cells that contains
your data list.
Click <OK>
This brings you back to the target WB.

NOW ... set-up your drop-down list.
<Data> <Validation> <Allow>, click on "list".
In the "Source" box, enter:
=ValidDepts
Then <OK>
And you're done!

Don't forget, both WBs must be open for this to work.