Home > Validation > Drop Downs > Other Workbook Drop Down List from Another WorkbookIn Microsoft 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. NOTE: Both workbooks must be open, any time you want to use the drop down lists. |
Create Workbook With Master ListThe first step is to create a master workbook, and a master list of items, in that workbook. In this example:
Tip: For instructions on creating a named range, go to the Naming Ranges page. If you are using your own file with a master list, substitute your master file's name and list name in the instructions below. |
Define Name for Master ListWhen the New Name dialog box opens, follow these steps:
|
Use the Drop Down ListTo use the data validation drop down lists, both workbooks must be open. Note: In the screen shot below, the customer order information is stored in a named Excel table. If you add a new customer order at the end of the table, the table will expand zutomatically, to include the new row. Also, an in-cell dropdown list should be added automatically, in any new rows.
In the screen shot below, there are drop downs in column A, where you can select a customer name. The master list of customer names is stored in a different workbook |
Get the Practice FilesDownload the zipped Drop Downs from Other Workbook sample files. There two files and both are in xlsx format. The files do not contain macros. When you unzip the files, keep both files in the same folder. Open the master file first (DataValWb.xls), and then open the other workbook. More Tutorials |
Last updated: December 21, 2022 3:41 PM