List reference in Excel 2007

[draacor]

Is there a way to create a drop down list in Excel where the data in the list references another workbook?

 

[AnotherHiggins]

another workBOOK or another workSHEET?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[draacor]

Another Workbook

 

[AnotherHiggins]

Nope. You have to use a Named Range to even refer to a different sheet within the same workbook. But it won't accept a Named Range from a different workbook.

You could put your list on a hidden sheet in the same workbook.

Or, if you really don't want other's to see it, you could set the sheet containing the list to "Very Hidden" - but this requires VBA.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[draacor]

ya im trying to work it so that i have one workbook with all the values that i can change, so that the other workbooks get updated automatically with the updated lists.

 

[Gavona]

s there a way to create a drop down list in Excel where the data in the list references another workbook?

Actually there is a way but the workbook with the list has to be open (but maybe hidden).

You need to use named ranges where the name references another sheet. So in Insert, Name, Define the RefersTo box looks like:
=[EraseMe.xls]Sheet2!$A$6:$A$10

The easy way to achieve this if like me you hate using Insert,Name,Define to define the named range and can't remember the syntax to refer to another workbook is:
Move the sheet with the list to your workbook.
Name the range containing the list in the normal way.
Use it in your data validation,
Move the sheet containing the list back to its original workbook.

Hope this helps.

Gavin

 

[AnotherHiggins]

Clever, Gavin.

But it seems the the workbook that contains the list has to be open or the drop down in the Validation is empty.

It doesn't sound like that will work for the OP.

But a different approach just occurred to me (I think this should work):

[tab]- In the workbook that users will access, insert a new sheet.

[tab]- Go to Data > Import External Data > Import Data.

[tab]- Browse to the target workbook that you'll be updating

[tab]- Select Sheet1 (or whatever the name is).

[tab]- Select a cell for the imported table to reside - I'd just go with A1

[tab]- Now, in the workbook that users will access, create a Dynamic Named Range (post back if you need help with this part), let's call it MyList, that will expand to include all values in column A

[tab]- Right click an A1, Select Data Range Properties

[tab]- Check the box beside Refresh data on file open

[tab]- Hide this worksheet

[tab]- Go to Data > Validation, Select List, For Source, put in MyList


Now, every time any user opens this workbook, it will refresh the list on the hidden sheet which will, in turn, update the drop down items!


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Oh, and obviously the target workbook that you update will have to be in a shared folder that all users have access to.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

A MUCH better better solution to OP issue.

My post shows a technique that can help to make formulae refering to ranges on another workbook shorter and easier to read.

Gavin

 

[AnotherHiggins]

Any luck, draacor?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[draacor]

I was able to achieve what i wanted by adding the worksheets in the reference workbook as datasources. It works great now, thanks for all of your help guys