Forum Discussion
How to create Dependent drop down list using two workbooks
Hello,
I am trying to create a dependent dropdown list which includes 2 separate workbooks (excel document). 1st is where the drop down data in saved and 2nd is where I want to create a dependent drop down list for a set dataset. Please advice.
5 Replies
Hi Avinash1720
it is technically not possible in Excel to use dropdown lists where the source is located in a different workbook. And even if it was, I would highly recommend not to do so, you would be in trouble sooner or later (source file gets moved, renamed, etc.)
Instead, if it is really necessary to keep the workbooks separated, I would recommend to dynamically import the source data into the target workbook using Power Query. This way, you have everyhing in one place to create proper dropdown lists. And on the other hand, you can continue to maintain the source data in a different workbook.
- Thanks for the response. I'll explore the power query option.
How to create drop down list from another workbook in Excel?
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the source data you need for the drop-down list locates in another workbook, how would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.
*Disclaimer: I provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.
In this upper link from microsoft you will find general information about drop down menus and a link for excel template with drop down examples.
Hope I was able to help you with this info.
NikolinoDE
I know I don't know anything (Socrates)
- Thanks for the response. I was able to create a drop-down list where the source data was in a separate excel document. However, was unable to find any solution for a dynamic/dependent drop-down. The idea was to have one common source file with the drop-down data that can be used by multiple users to create drop-down list in their excel doc. That way we can update only one source file with the drop down data instead of multiple.
- As far as I know, it is possible to drop down from another workbook.
With the drop-down list in workbook A and the source in workbook B, it is possible to use the validity list to define a name (Ctrl + F3) that refers to a cell range in the other workbook. in the upper post is the example link. It could also be done with VBA.
I don't know whether it is possible in all versions of Excel, but the exact version of Excel is not given by the user.
I am aware that this is not the best way and can be fraught with many problems. I can only support that Power Query could be the best solution. But why the user would like it that way, or what project it might be, I don't know and was not asked by the user.
