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
The answer is Yes you can! Here’s how.
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.
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.
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!
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.
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”.
And that is it. In the cell that you have chosen, a dropdown list that has a source in another workbook is now active.
Related Posts
- October 14, 2014
Probably the longest title of all times, but it leads to ...
- September 2, 2014
Our goal here, is to create a dropdown list by using Data ...
- July 1, 2013
Creating a dependent or linked Dropdown list In this article I ...
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
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
Hey Corwin,
if you are using Excel 2013 and are finding this solution unsatisfactory, you should try this solution
https://excelunplugged.com/2014/10/14/data-validation-dropdown-power-query/
i tried exactly as you say but excel 2013 just don’t want to do id, both workbooks are opened but nothing?
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”
“Games”
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.
Use Power Query to get data into this workbook
https://excelunplugged.com/2014/10/14/data-validation-dropdown-power-query/
and then just do a normal dependent data validation
https://excelunplugged.com/2014/09/02/dependent-data-validation-dropdown-lists/
and that should work.
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
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.
I’ll the give the latter a try first. Thanks so much for the quick response.
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?
Without Power Query you should have the workbook opened. You could go for a VBA solution that would open the appropriate workbook on demand.
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.
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.