Excel Unplugged

Creating a Data Validation dropdown list from another Workbook

Can you create a Data Validation Dropdown list that uses data from another workbook as a source?

This question has been proposed to me continuously since I published two Data Validation related posts:

Taking the Data Validation List to the next level

Dependent Data Validation

The answer is Yes you can! Here’s how.

How to

First you open a workbook where the source cells for your dropdown reside. Then you define a name for that range of cells. You have to do that because once you open the Data Validation window you cannot refer to another workbook as a source for the dropdown list. But this can be overcome by creating a few named ranges in our source and destination workbooks. I would recommend that both the name of the range and the name of the source workbook do not include spaces and are simple. In the sample those names will be Source.xlsx and Months.

Here’s how you assign the name in the Source workbook. First you select the range of cells that would serve as a source for your Data Validation Dropdown. Then you write the desired name in the Name Box and press ENTER. In the sample below the name given was Months.

Creating a Data Validation Dropdown list from another Workbook

Edit the “destination” workbook

Now we go to the “destination” workbook (where we wish to have our Data Validation Dropdown List). And here we must create a Named range that will refer to the named range in the Source Workbook. This trick will allow us to refer to the named range in this workbook when we will create a dropdown list, but doing that we will indirectly be referring to another workbook. We will create a named range in the Destination workbook by using the Name Manager.

Creating a Data Validation dropdown list from another Workbook

So you go to Formulas/Name Manager/New and in “Refers To” you write ‘NameOfTheOpennedWorkbookWhereTheNamedListIs.xlsx’!NameOfTheRange in our sample that is Source.xlsx!Months. And the name given is MonthsDestination.
Do not forget, the source workbook must be opened when you are doing this!
Creating a Data Validation dropdown list from another Workbook

This will give you a named range in the target workbook that is equal to the one in the source but with one vital difference. This range can be referenced as a source for the Dropdown List as it resides in the Destination workbook. So now you can select the cell where you wish the Dropdown to be and go to DATA/Data Validation.

Creating a Data Validation dropdown list from another Workbook

In the Data Validation window you select List under Allow and write the “=name” that you defined in the Destination Workbook. In our sample that is “=MonthsDestination”.

Creating a Data Validation dropdown list from another Workbook

And that is it. In the cell that you have chosen, a dropdown list that has a source in another workbook is now active.

Creating a Data Validation dropdown list from another Workbook

Comments 15

  1. Frank Byl says:

    Love it. We created sales reporting sheets for different sales staff. By having the data validation centralized it allows us to update the lists from one central place.

    An additional extension on what you have done is to format the named range in the source file as a table. If the table is exactly the same size as the named range then when more items are added to the list the named range automatically expands. It is also possible to remove a row from the list and have the data validation automatically contract. Nice thing about that is it won’t change old data in the reports.

    Thanks for the great ideas

  2. Corwin says:

    Unfortunately this only works as long as you have the source workbook open , if you close it it will no longer work and lock the validation

    1. Hey Corwin,

      if you are using Excel 2013 and are finding this solution unsatisfactory, you should try this solution

  3. Zlatko says:

    i tried exactly as you say but excel 2013 just don’t want to do id, both workbooks are opened but nothing?

  4. Jacob B says:

    Hey, I’m trying to set up a bunch of drop down lists in one central location. These drop downs are being referenced in other workbooks. however in the other workbooks, i need an indirect reference.

    Example: (using games instead of client information)

    Named drop down is “System”
    System has “XBOX One” and “PS4”

    Indirect reference is needed to give either the XBOX One list for Games, or the PS4 list for games dependent on the previous selection.

    I can get this to work when the drop downs are defined in the same workbook, but when they’re in another workbook it wont.

  5. C Yocum says:

    This works fabulously if I only open and use the documents on my computer. However several of us use the source and destination documents in Dropbox on our respective When another user opens the documents on their computer, the “Refers to” changes from the simple source same to the last user’s full path name.

    Is it possible to keep the “Refers to” field adaptable to all computers?

    Example: Refers to: =TablesAll.xlsm!Cat_Desc

    When another user opens the file: Refers to: C:/username/dropbox/fullpathname/TablesAll.xlsm!Cat_Desc

    Hopefully you can help us

    1. Sadly you can’t get to windows usernames with Power Query. You could do that in Excel (with VBA) and then call that Excel to get the user info and then dynamically put the path together…

      Another approach would be to try and uniform a path for everyone.

      1. C Yocum says:

        I’ll the give the latter a try first. Thanks so much for the quick response.

  6. Naveen n says:

    Thanks for the article.
    even I have the issue, when the source excel is closed the drop down list is empty.

    Also I do not have power query in my excel (its not installed in my office machine).

    Do we have any other way to do this?

    1. Without Power Query you should have the workbook opened. You could go for a VBA solution that would open the appropriate workbook on demand.

  7. Anish says:

    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.

    1. The only way to do this, is to use Power Query to dynamically get the desired data to the target workbook and then you can do the dependent data validation by any of the methods I described.

      1. EM says:

        Hi, how can I do this? I have find a lot of solutions but still can’t get one. I need your help please.

        1. Honestly, the best way is to create a Power Query connection to the other workbook and load only unique and maybe even sort within Power Query. Then load the result into a table and use that as a source for your Data Validation List.

Leave a Reply

Your email address will not be published. Required fields are marked *

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