create drop down list from different workbook

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

Brett

Check out the steps Debra Dalgleish outline here.

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


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 12:49:02 -0700, Brett <[email protected]>
wrote:

>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

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Brett" <[email protected]> wrote in message
news:[email protected]...
>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