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