Copy drop down list in Excel
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. Show IntroductionA 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. 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:
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. Create a Reference to Master ListNext, 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.: Create the Drop Down ListNext, you can create one or more drop down lists, based on the name that you just created.
Use the Drop Down ListTo use the data validation drop down lists, both workbooks must be open.
Video: Drop Down List from Another WorkbookTo see the steps for creating the data validation drop down from a list in another workbook, watch this short Excel video tutorial. Get the Sample FilesGet 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) More TutorialsDrop Down List Basics Hide Previously Used Items Data Validation Tips Don't Miss Our Excel TipsDon'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. |