You can use a list from another workbook as the source for a Data Validation dropdown list The following instructions are for Excel 2003 and earlier versions. On the Contextures blog there are instructions for using a list from another workbook in Excel 2007 and later.
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. You can try these steps in your own workbooks, or download the sample files for this tutorial.
IMPORTANT: For the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists. You could create the list in a workbook that is always open, but hidden, such as the Personal.xls workbook.
The first step is to create a master workbook, and a master list of items, in that workbook. In this example:
If you are using your own file with a master list, substitute that file's name and list name in the instructions below. For instructions on creating a named range, refer to Naming Ranges.
Next, open the workbook in which you will create the drop down lists. Then, follow the steps below, to create a new name in that workbook
NOTE: If the workbook name has spaces, type an apostrophe at the start and end of the name, e.g.:
='Data Val Wb.xls'!CustName
Next, you can create one or more drop down lists, based on the name that you just created.
To use the data validation drop down lists, both workbooks must be open.
To see the steps for creating the data validation drop down from a list in another workbook, watch this short Excel video tutorial.
Download the zipped Drop Downs from Other Workbook sample files. There two files and both are in xls format. The files do not contain macros. When you unzip the files, keep both files in the same folder. Open master file first (DataValWb.xls)
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: October 10, 2020 2:32 PM