In Excel, you can create a drop down Data Validation list, using a list from another workbook as the source.
However, for the data validation to work, the workbook that contains the list must be open, in the same instance of Excel. Users will have to open both the workbooks -- the one with the drop down lists, and the workbook with the original source list.
The following instructions are for Excel 2007. There are similar instructions to create a data validation list from a different workbook in Excel 2003.
Create the Source List
In this example, you have a workbook named DataValWb.xlsx, which contains a list of customer names. This list is in a range named CustName. (For instructions on creating a named range, refer to Naming Ranges.)
Create a Named Reference to the Source List
- Open the workbook that contains the source list -- DataValWb.xlsx in this example.
- Open the workbook in which you wish to use the list in Data Validation, or create a new workbook.
- On the Ribbon, click the Formulas tab, then click Define Name.
- Type a name for the List, e.g. MyCustList
- From the Scope drop down, select Workbook
- In the refers to box, you'll type a reference to the named range in the source workbook:
- Type an equal sign, then the source workbook name and extension
- Next, type an exclamation mark, followed by the range name in the source workbook.
- In this example, the completed reference is:
=DataValWb.xlsx!CustName - NOTE: Type an apostrophe (') at the start and end of the referenced workbook name, if it contains a space character. For example:
='DataVal May10.xlsx'!CustName
- Click OK
Create the Dropdown List
- Select the cells in which data validation will be set.
- On the Ribbon, click the Data tab, then click Data Validation
- In the Allow box, choose List
- In the Source box, type an equal sign, then the list name, e.g.: =MyCustList
- Click OK
Use the Data Validation List
To use the data validation drop down lists, both workbooks must be open.
- Open both workbooks -- the one that contains the drop down lists, and the workbook that contains the original source list.
- Select an item from the data validation drop down list.
Watch the Video
To see the steps for creating the named ranges, and the drop down lists, you can watch this short video.
You can also watch the video on YouTube: Excel Drop Downs From List in Different Workbook
____________________
Debra:
What happens if this workbook is used to fill in a form, for example a report, where the validation is a client name, and the workbook is given to someone else, who dose not have the workbook with the data source. Will the data field still be filled in, or will an error message show up?
Stan, without the source book open, the user should see a prompt about updating links. They could click No to that prompt.
None of the selected values would be affected, but the drop down lists wouldn't work.
If I needed to share it with other people, it seems like I would just place the data in the same workbook on a different sheet.
Rick, that's what I'd do too, but a surprising number of people ask how to use a list in a different workbook.
[...] Fijate en este post en el blog de la canadiense Debra Dalgleish. Contextures Blog Data Validation List From Different Workbook [...]
http://blog.contextures.com/archives/2009/05/12/data-validation-list-from-different-workbook/
I am trying to do the same thing as instructed on the above weblink, to create my data in another work book, named and saved it, but when I go for another work book for data validation it keep saying that there is no such name in the range. Help ??
I use this method to share some works with some workers. Would you be so kind to explain how exclude any worker after using Combo Box (Excel 2007!)
It simply does not work in excel 2007, I have tried this many times but still it does not!
Are we going backwards with office 2007?
Pat, it works for me in Excel 2007. What happens when you try it? Do you get an error message?
In the help facilities it does stipulate that validation cannot be done with an external workbook! The only way round is to create a macro to open this external workbook, hide it, to pick up the data from (list).
I have been working with vb, vba, access, excel for 18 years!
Yes Pat, you're right, this technique only works if the workbook with the original list is open too.
I was struggling to make this work Pat until you revealed that both w0rkbooks have to be opened. You would think the original author would have noted this. Anyway, how do you trigger your macro? Could it be made to run automatically when the workbook is opened? I don't want to have to issue intructions to click a button if I can help it.
Paul, glad you were able to get this working. In the article, I did mention that the other workbook has to be open -- it's in bold, in the sentence under the first screenshot. Maybe I'll add that as a note, at the end of the instructions too, to make sure that everyone see it.
You could record a macro as you open the other workbook. Then, add that code to the Workbook_Open event code, for the workbook with the data validation drop downs.
Hi Deborah, I assumed you meant that both workbooks had to be open in the same instance of excel whilst setting up the link. It wasn't clear that the link would then essentially break when the source workbook is closed.
I'm not sure how I would add the macro to the Workbook_Open event code – would this have to be done in VBA? Can you give me an example of how you would do this or point me in the direction of a tutorial?
Many thanks!
CAN U HAVE 10 SHARED WORKBOOKS USING A SINGLE SOURCE WORKBOOK, FOR A DROPDOWN MENU;
BUT I NEED TO HAVE LIKE 5 BOOKS OPEN AT A TIME FROM DIFFERENT COMPUTERS OVER A NETWORK. PLUS EACH ONE NEEDS TO HAVE A FUNCTIONING DROPDOWN OPTION.
CAN THIS BE DONE?
Please tell me about data validation in excel 2007
Not sure if Lokesh's post is spam or not, but here is a page I created on Data Validation in Excel 2007 and will work in Excel 2010.
http://bohr.wlu.ca/cp212/201005/excel_data_validation.htm
Thanks Rick, and I'm not sure what Lokesh wants to know, because this blog post showed the steps in Excel 2007.
And nothing has really changed from earlier versions, except the command is on the Ribbon now, instead of the menu bar.
Thanks Rick Henderson.
I need sm details reg. macros. I'm using xl for create invoice, and I want to save all invoices in a single sheet. possible?
Debra,
Thanks for this solution – it saved a lot of hassle for me.
One thing that you could ad to the instructions:
You have to use apostrophe (') in the reference workbook name if it has a space.
Example: ='DataVal May10.xlsx'CustNames
(It might be obvious to most of you, but not for the less advanced programmers)
Thanks again!
Laz, thanks -- I've added that note to the instructions.
I followed the steps, have both workbooks open, but I still get the error message saying it cannot reference to another workbook.
Hi Anderson,
it happened to me too. My problem was that I kept copying an invalid link that used square brackets [] at the beginning and at the end of the file name and also reported the Sheet name. Example: ='[DataVal May10.xlsx']Shhet1'!CustName. I deleted the square brackets and the sheet name and everything worked fine. Don't know if it's the same for you, but give it a try.
Follow step by step the guide (without using any short cut or copy and paste) and you should be fine
In excel 2007, I am trying to create a validated list referring to a list of e-mail addresses. But when I access the data, it is losing the e-mail address link and all I get is the typed address and no link which is not helpful for what I want to create.
Anybody has an idea of how to deal with that problem?
thanks
Claire
Thank you very much
It works beautifully, but still have I a question about closed workbooks. How is it possible?
I am trying to use data validation in a Czech version of Excel 2007. I have a list of names in one sheet, marked as a named range. When I try to use data validation to restrict entries to the list in a cell on another sheet, I get an error message saying that the criteria for data validation does not allow references to other worksheets or workbooks. But when I use a direct reference, (=OtherSheet!A1:A9), it works fine. The problem is, the list changes, so a named range is more appropriate. I cannot put it on the active sheet, those get created and deleted by the user and my list needs to be available to all sheets. The only way I have found to make it work so far is to use =OtherSheet!A:A to reference the entire column. That works, but the dropdown then has many blank entries, and the "Skip blank cells" check box has no effect on this. Does anyone know how to deal with this?
Oh My God.
You are amazing, I've bee searching for like a week now for the solution to my drop dowm list problem, and this article was PERFECT help.
Thank you so so much.
I have an interesting question Debra. Any idea why you can't use another worksheet in the same workbook as a datasource? I've tried this a few times and it doesn't see to work. I was hoping to use something like a Named Range to store my valid choices but it appears that range has to appear somewhere on the current worksheet... though you could hide it or place it out of view.
Rick, if you name the range (e.g. MyList) on the other sheet, you can refer to that name in the data validation source: =MyList
Maybe you didn't include the equal sign when you tried it? Excel Data Validation
You're right of course Debra! I must have forgotten that bit. Great page of info!
Brilliant. Gives me the solution to accessing data from a table on another sheet in the workbook. I was trying to refer to the cells address. Now I know just to name the group it's so much easier. The fact that tha data was in a table confused me.
Works great, but one more bit of advice needed please. I have a folder set with data linked between workbooks, but when I move the folder to another location, the validation stops working. When I look at the 'refers to' in the name manager of the destination file, the path refers to the original loaction of the folder set.
How do I stop this unwanted renaming of the source data.
Hi – I have tried using the instructions to pull down a drop down validation list in one workbook using data source from another workbook. Have them both open and used same file names as in example but still get error saying I can't use data from an external source. I don't quite understand though, how excel can 'find' the data source from just the range, i.e. =MyCustList in source of data validation on one workbook and expect to find it with no other information from another workbook.
Thanks! This is very useful!
Roy, I'm hoping someone answered you, or that you figured it out. I have the same issue and would like to hear about a solution.
@Roy and @Les, if you open the source workbook first, and then the workbook with the drop down lists, does it update the links correctly?
@Barbara, the connection to the other workbook is in the definition for the MyCustList named range that you create in the second workbook.
Here is the definition for MyCustList -- it points to the CustName named range in the DataValWb file.
=DataValWb.xlsx!CustName
Thanks for the information provided here – it was very useful in the office. However, I have a difficult situation here that you may know something about (I'm taking the risk regardless!).
I have an Excel template that my colleagues will use to produce other spreadsheets. It has several drop down menus that connect to a separate data workbook using the method described above. The issue here, then, is that I don't want to have to make everyone open up the data workbook before the template workbook, so I'm instead aiming to write a batch file that my colleagues will launch so that the data workbook is opened before the template.
The batch file I have written does this just fine, but when the template is opened it requires that links be updated; not what is wanted! However, if I manually open the data workbook then the template workbook, no update is required. Any ideas?
@eruditio
Have you tried clicking "start up prompt" in "Edit links"? You can then choose to update the links automatically
Deb, please help, I've been running around the internet forever and can't figure this out. I've had a source document (MoM Data.xlsx) and template document (_MoM Template.xlsx) that have been linked for years with dropdowns working just fine. As of 3 weeks ago, I get an error that "...one or more links cannot be updated...". When I look to see where it's trying to pull the source from, it seems to have put an extra "Server's Documents" in the path. No matter what direction I try to take I can't: break links, change the source to a different file, edit the path to the proper file, etc. Any ideas?
@Chad, that's odd, and I haven't seen that happen before. Can you delete the bad link and rebuild it?
I have but that just let's me ignore the error. It doesn't actually fix the connection issue.
By "delete the bad link" do you mean delete the source file, recreate the source file, and then tell the 'result' file to look for the newly created source file? The "MoM Template" result file in my case doesn't seem to allow me to change the source file at all. I have been able to recreate the "MoM Template" from scratch and tell it to attach to a source file (to force the change) but I get the same problem. Another piece of info that may help shed some light ... the "MoM Data" and "MoM Template" files are in 2 different folders on the same machine but then accessed across the network by a number of users. Could this be a 'path' issue?
Another option in my opinion is to Create a DataQuery to get the list of Customer Names from the source workbook (connected by network) and NamedRange that portion that will fill combo-box.
This will neither required Enableing Macro nor you have to open the Source workbook.
Murtaza
Yes, I like that approach too.
The video is very specific as to how to do this, and I have done it many times, but to no avail. I get a message that "the source currently evaluates to an error. Do you want to continue?" when I enter a valid code from the separate open workbook.
@Leo do you see the drop down list that shows the items from the other open workbook, or are you not able to create the list?
Also, make sure you're using the correct spelling for the name in the other workbook -- it's easy to miss a letter, or add an extra one.
Long confusion with regard to the dropdown list from different work book is solved. Thanks alot. The video was very precise.