how do i create a drop down list of items from a different file

I need to have a file that stores a list of projects. This file will be
updated on a regular basis. In another file I need to have that list
populated into a drop down selection. When the user opens the file it should
auto update any changes to the project list that is in the different file. I
have tried to do it using data validation but it will only pull the values in
if I have both files currently open. I would like to have the project list
file closed on a network and have the other sheet still pull the info in.

How can I get this to work?

Use a range in the drop down list workbook that directly references the range
in the 'closed' workbook, i.e.,

='[other book.xls]Sheet1'!A1
='[other book.xls]Sheet1'!A2

These formulas will work, even with 'other book.xls' closed, and use this
range of formulas as the list for your dropdown



"Profnutbutter" wrote:

> I need to have a file that stores a list of projects. This file will be
> updated on a regular basis. In another file I need to have that list
> populated into a drop down selection. When the user opens the file it should
> auto update any changes to the project list that is in the different file. I
> have tried to do it using data validation but it will only pull the values in
> if I have both files currently open. I would like to have the project list
> file closed on a network and have the other sheet still pull the info in.
>
> How can I get this to work?

How do I enter it in? If I enter it as a list it tells me the formula is
invalid. If I enter it as a data validation list it says that I can't
validate outside data.

"Duke Carey" wrote:

> Use a range in the drop down list workbook that directly references the range
> in the 'closed' workbook, i.e.,
>
> ='[other book.xls]Sheet1'!A1
> ='[other book.xls]Sheet1'!A2
>
> These formulas will work, even with 'other book.xls' closed, and use this
> range of formulas as the list for your dropdown
>
>
>
> "Profnutbutter" wrote:
>
> > I need to have a file that stores a list of projects. This file will be
> > updated on a regular basis. In another file I need to have that list
> > populated into a drop down selection. When the user opens the file it should
> > auto update any changes to the project list that is in the different file. I
> > have tried to do it using data validation but it will only pull the values in
> > if I have both files currently open. I would like to have the project list
> > file closed on a network and have the other sheet still pull the info in.
> >
> > How can I get this to work?

Open both workbooks, use Window / arrange / tiled. Now click on a cell in
one, type = and then click on a cell in the other. You just created a link.
Close the workbook you are linking to and you will now see the full filepath
in the formula. You can now copy this down as far as you like.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Profnutbutter" <Profnutbutter@discussions.microsoft.com> wrote in message
news:D1509DC9-C02B-4803-8CDC-B7885F7BD19A@microsoft.com...
> How do I enter it in? If I enter it as a list it tells me the formula is
> invalid. If I enter it as a data validation list it says that I can't
> validate outside data.=
>
> "Duke Carey" wrote:
>
>> Use a range in the drop down list workbook that directly references the
>> range
>> in the 'closed' workbook, i.e.,
>>
>> ='[other book.xls]Sheet1'!A1
>> ='[other book.xls]Sheet1'!A2
>>
>> These formulas will work, even with 'other book.xls' closed, and use this
>> range of formulas as the list for your dropdown
>>
>>
>>
>> "Profnutbutter" wrote:
>>
>> > I need to have a file that stores a list of projects. This file will be
>> > updated on a regular basis. In another file I need to have that list
>> > populated into a drop down selection. When the user opens the file it
>> > should
>> > auto update any changes to the project list that is in the different
>> > file. I
>> > have tried to do it using data validation but it will only pull the
>> > values in
>> > if I have both files currently open. I would like to have the project
>> > list
>> > file closed on a network and have the other sheet still pull the info
>> > in.
>> >
>> > How can I get this to work?