Excel Drop Down List From Different Workbook

Excel Drop Down List From Different Workbook

In Excel, you can create a drop down Data Validation list, so it’s easy to enter valid items in a cell.

Usually, the list of valid items is stored in the same workbook, but it’s possible to set up a drop down from a list in another workbook — as long as the other workbook is open.


The instructions are below, and watch the video to see the steps. The demo uses Excel 2010, and it’s the same in Excel 2007, except that there is an Office button, instead of a File tab.

Drop Down from a List in Another Workbook

Important Note: 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 and later. On my website, there are similar instructions to create a data validation list from a different workbook in Excel 2003.

drop down from a list in another workbook
drop down from a list in another workbook

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

Next, you’ll create named range in the workbook where the drop down list will be added.

  1. Open the workbook that contains the source list — DataValWb.xlsx in this example.
  2. Open the workbook in which you wish to use the list in Data Validation, or create a new workbook.
  3. On the Ribbon, click the Formulas tab, then click Define Name.
    • DefineNameCmd
  4. Type a name for the List, e.g. MyCustList
  5. From the Scope drop down, select Workbook
  6. Click in the Refers to box — this is where you’ll type a reference to the named range in the source workbook
  7. Type an equal sign, then the source workbook name and extension. Do NOT include any square brackets in the name. For example:
    =DataValWb.xlsx
  8. OR, if the workbook name includes spaces, start and end the name with an apostrophe. For example: ‘DataVal May10.xlsx’
  9. Next, type an exclamation mark — !
  10. Finally, type the range name in the source workbook
  11. The completed reference should look like this:
    =DataValWb.xlsx!CustName
    OR =’DataVal May10.xlsx’!CustName
  12. Click OK

NewNameCustList

Create the Dropdown List

  1. Select the cells in which data validation will be set.
  2. On the Ribbon, click the Data tab, then click Data Validation
  3. In the Allow box, choose List
  4. In the Source box, type an equal sign, then the list name that you just created, e.g.: =MyCustList
  5. Click OK

DataValMyCustList

Use the Data Validation List

Remember, to use the data validation drop down list, both workbooks must be open.

  1. Open both workbooks — the one with the drop down lists, and the workbook with the original source list.
  2. Select an item from the data validation drop down list.

Watch the Video

Watch this video to see the steps for creating a drop down from a list in another workbook. You’ll see how to set up the named ranges, and the drop down lists. The demo is in Excel 2010.

________________________________

83 thoughts on “Excel Drop Down List From Different Workbook”

  1. 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?

  2. 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.

  3. 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.

  4. […] Fijate en este post en el blog de la canadiense Debra Dalgleish. Contextures Blog Data Validation List From Different Workbook […]

  5. 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!)

  6. 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?

  7. 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!

    1. I actually saved my DataValWb.xlsx in the “xlstart” folder so i dont have to open it every time.

  8. 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.

  9. 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.

  10. 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!

  11. 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?

  12. 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.

  13. 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?

  14. 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!

  15. I followed the steps, have both workbooks open, but I still get the error message saying it cannot reference to another workbook.

  16. 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

  17. 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

  18. Thank you very much

    It works beautifully, but still have I a question about closed workbooks. How is it possible?

  19. 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?

  20. 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.

  21. 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.

  22. 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.

  23. 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.

  24. 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.

  25. 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.

  26. @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

  27. 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?

  28. @eruditio
    Have you tried clicking “start up prompt” in “Edit links”? You can then choose to update the links automatically

  29. 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?

  30. 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?

  31. 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

  32. 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.

    1. @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.

  33. Long confusion with regard to the dropdown list from different work book is solved. Thanks alot. The video was very precise.

  34. I’ve read many of the responses, but I am still wondering how I can work around opening the source workbook? We have a list of values that will be common for every project, so it makes more sense to have one source workbook, with all the values listed, and then the project workbooks in the working directories. I am using Data Validation to try and make the lists (which is working) but it stops working when the source is closed.
    If there is something else that I should be looking at, other then data validation, then I am open to that.

  35. Come on!!!
    This is very simple…
    You don’t need to do complex naming process like this.
    Just use indirect function!!!
    =indirect(“[Book2]Sheet1!$A$1:$A$10”)
    (Book 2 A1 ~ A10 should have validation list)

  36. Debra,
    On the Excel Expert exam for 2007 there is an objective for referencing external data in a drop down list under restrict data entry by using data validation. What would they be referring to on this exam objective on the Microsoft exam?

  37. I am getting an error when I try to save the source in Data Validation which says “the ;list source must be a delimited list or referenced to a single row or column”

  38. I want to keep data file (source file) in server computer which is confidential and number of users should access the only some cells in common source file using data validation list from other network shared computer.Is it possible? If yes please describe me.
    Thanx

  39. I have followed these steps to the T, including using the names (they just happen to work in my case). But when I get to the Data Validation step to pick the source it says “The Source currently evaluates to an error. Do you want to continue? If I choose yes, there is nothing in the drop down if I say no, nothing happens at all.
    Windows 7, Office 2010 I am attempting to place the data file in my Personal excel file as I have to use this data each month i do not want to place it in my end file (which is already large enough). Any help is greatly appreciated!

  40. A simple way to get around the issue of having to have both speadsheets open is to create dynamic links between the source data workbook and a worksheet in your destination workbook. Then just use the linked data for your named range(s) which is now inside the same workbook.

  41. Have both files open is not an issue for me, as I use macros fairly often I always have my Personal file open. I just need to get past this Source error.

  42. I have exactly the same error message as Alice experienced when following precisely the procedure described. It seems not to applicable for office 2010/Windows 7/64bit. Anybody succeeded in such an office/system environment?

  43. I seem to keep getting the same error message when I try to define the name of the cells. I am exporting a spreadsheet from another program into an excel sheet which automatically the spreadsheet “Book!” and my information is on “Sheet1” so I enter this =’Book1′!sheet1!$E:$E to have it name the row “E” and the error states the standard formula contains an error and highlights the first $E.

  44. Here’s what I’m trying to do…
    A centrally held spreadsheet is owned by department A who regularly update the item lists and named ranges to keep their catalogue up to date.
    Various other departments need to reference the named ranges they have to create their own drop down lists and use data validation (e.g, finance need the catalogue item for recharge info, the engineering department to use standard items in their designs etc.,)
    So… creating a sheet for one of these departments…. I have created a macro that loads the central sheet upon opening so I can then reference the named ranges in that list as per instructions here.
    However, I now need to use the INDIRECT function to provide a sublist in my sheet based upon the selection form the dropdown the user selects.
    e.g,
    Sheet A contains “Produce” named range which con taints “Fruit” and Veg” as items. NAmed ranges called “Fruit” and “Veg” hold list of…wait for it…. fruit and veg 🙂
    Sheet B needs to open sheet A, and have a dropdown data validation list that provides the choice of “Fruit” and Veg” in Column A. Upon the user selecting “Fruit” in spreadsheet B Column A, the Column B would then need to provide another dropdown with only selections from the Fruit named range in spreadsheet A.
    Hope that makes sense…. trying to do this I can get column A working but when trying to use =INDIRECT(A2) in column B excel tells me the source is evaluates to an error.

  45. Hello David, I am also caught up with the same problem. I hope you share the solution if you come across it. thanks 🙂

  46. On trying to create list in the cell, a message pops up: “You cannot use references to other worksheets or workbooks for Data Validation criteria”
    Anybody knowing reason and solution for same???
    Please reply
    Thanks in advance

    1. Atul, I get the exact same problem you describe. Did you manage to find a solution? I am using Excel 2007. Your post is 2 years old now, I hope you remember what this was about? Do let me know.

  47. Hi I followed your instructions in the other page on how to create a combo box in a workbook that has data validation and it was such a good piece of work however i have to problems the first problem is i want to make my drop down list searchable in this way i want to search for a character like MR and it gives me all the words that have MR in them regardless of the position in the word whether it’s the first middle or last character in that word.
    Secondly i wouldn’t like some one entering wrong data in the excel workbook boxes i need them to get an error but when i enable the error box during the validating data it doesn’t show the error once i run the code to make those cells searchable…………Pleeeeasseee Help it Urgent i kindly ask.

  48. Hi Debra, I was following your thread on this subject and should be glad for your kind help here. I have an Invoice template for which I need to pull the name and address details of the invoice from a database. I am trying to use a drop down validation for the name cell and vlookup for the address cells. The database has first name, second name and last name columns. May I know, how best do I use the ‘concatenate’ function or indeed any other function you might prescribe to combine the first name and last name onto the ‘Name’ cell of the Invoice template. The Data Validation Source box is giving me an error message when I try to combine the names from the database. I believe that if I can get this right, then I’d be able to use vlookup to automatically insert the addresses based on the data on the ‘Name’ cell on the Invoice. Please help.
    Thanks.
    Kenny

  49. Hi Debra, To further explain what I have done so you’d have all the information to advise me: The first three columns of my database are: Title, FirstName, Middle Name, LastName. But I want to extract the Title, FirstName and LastName onto the Invoice Name & Addresses. On the database, I highlighted the Title column, went to ‘Format, define name, and I named it. I did same for the FirstName and LastName columns, respectively. Then on the separate sheet which houses the Invoice template, I clicked on the cell where I want the name to appear. Then I chose Data, Data Validation & List. For me, the problem arises from what I should enter in the ‘Source’ box in order to pull together the Title, FirstName and LastName from the database onto the Invoice ‘Name’ cell. As I indicated in my earlier post, I tried to use the concatenate function as follows in the ‘Source’ box: =concatenate(Title, ” “, FName, ” “, LName). But when I click OK, I get the following error message – ‘The source currently evaluates to an error – Do you want to continue’. I believe that I’m doing something very wrong. Please help.
    Thanks.
    Kenny

  50. I need some details regarding macros. I’m using excel 2007 for create invoice, and I want to save all invoices in a single sheet. possible?

  51. I followed the instructions on this page, and the drop-down menu referencing my source list works fine. But I can’t get the error alert to show when invalid data is entered. Any suggestions? Thanks.

  52. Is it possible to have one source file saved on a network, that other files reference, plus this source book would have to open and hide itself each time the original file is open? What code is used to open and hide the source book?

  53. Hi Debra
    Great article a step through of how to create a data validation list from another workbook. It’s worth mentioning that with Excel 2010/13/Office 365 it is possible to use SharePoint sites to reference your “list” from and then accessing through an Excel Web App, so hopefully reducing the amount of work-arounds you have to do. More information can be found: https://support.office.com/en-nz/article/Use-a-shared-workbook-to-collaborate-b8207fe9-a613-483f-b804-0ca4658b0c61

  54. Hi Debra,
    I have created a master source list, and wanted to use it for a new worksheet that about 40 people will use. I wanted to be able to use drop-downs that come from a separate source worksheet because there are constant updates to the information, and I didn’t want 40 people to constantly have to update their form. I figured if I could update one master worksheet, and have the other sheets always pull from it with drop down options, it would be perfect. Is there a work-around or a way to do this without having to have the source file open?
    Thanks

  55. Hi everyone
    This lesson was super helpful. I read through the comment list and did not find my question already answered. The one thing that I would like to alter is after we named the source CustName it was a set selection of cells. can we make that group of cells change automatically as my CustName changes over time? I thought maybe =offset formula would work but it does not. just like if the range was within the same workbook.

  56. I have multiple workbooks that could use a lot of information from one source (another workbook). But any time the source data is changed, I am stuck going into each workbook and making the changes to each one. That means opening each one up, unprotecting workbook, unhiding the source worksheet, unprotecting the source worksheet, making the changes, hiding, and then protecting the worksheet. When you have to do this for 8 workbooks it gets time consuming and annoying. I have tried for days to generate drop down lists that were supplied from a “sourcedata workbook” without success. If I had one workbook with the source data that I could go into and change instead of 8 different workbooks it would make my life so much easier. Unfortunately Excel 2016 will not let you do this even with both workbooks having to be opened. I guess I will have to learn how to write codes to get my desired results.

  57. Can we use the indirect function in this scenario as well to base the list of items in a drop down based on a selection in a previous column? Would be great if you could show how we can do it.

  58. Hi, after referring named ranges from another workbook as per the method above I am not able to use those ranges for creating dependent validation lists. First level validation is ok but when I use Indirect function to create validation list dependent on first one the excel doesnot take it. Please help.

  59. Hi

    I know this question has been asked a number of times and I still can’t find an answer…

    I understand how to create a drop down list of data items in 1 workbook and have it available in a different workbook.. The issue I have is that the method I used REQUIRES both workbooks to be open for the data reference to work… I WANT to be able to have a drop down list in one workbook WITHOUT having to have the source of the drop down data to be open…

    Does anyone know how to do this??
    That is,
    1. Source Workbook that has data for Destination workbook to display – CLOSED
    2. Destination of data to be displayed from Source workbook OPEN

    OH using Excel 365 and SharePoint to store both source and destination workbooks.
    Thanks for any insight

Leave a Reply to Roy Kirkham Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.