Save Time by Linking
While you can create an Excel drop down using worksheet or workbook, it is important to understand before you begin that the worksheet with the source list must be open for the data validation tool to work. In the separate workbook or spreadsheet, select the range of cells that will become the source list. Click inside the Name Box, which is the blank space above column A to the right of the formula bar. Type in a name for the range of cells and press Enter. (The name must be a single word.)
Now go back to the Excel worksheet in which you want to create a drop down list. In the Insert menu of Excel 2003, point to Name and select Define. In Excel 2007, go to the Formulas tab. Click Name Manager in the Defined Names group. The Define Names dialog will open.
Enter a name for the list under Names in Workbook. For this example, we have simply named it List. In the Refers To box, type the name range for the source list in the separate worksheet. This is the formula you will use: =workbookname!rangename. For example, the sample source list is in a workbook named Book1. We named the list LastNames. Therefore, for this example, the formula would be =Book1!LastNames.
After entering the formula, click OK.
Select the cells in this separate worksheet that you want to contain a drop down list. In Excel 2007, click on the Data tab and click the Data Validation drop down arrow. Select Data Validation from the list and the Data Validation dialog will open. In Excel 2003, go to the Data menu on the toolbar and select Data Validation. Make sure you are on the Settings tab of the Data Validation box.
Under Validation Criteria, click the drop down arrow under Allow and select List. Type the name you entered under Names in Workbook in the Define Names dialog, preceded by an equal sign (=), in the Source field. For our example, we chose the name List, so under Source we will enter =List. Click OK and a new drop down list will be created in the selected cells using the source list from a separate workbook.
For another way to have the source list on a separate worksheet, read Store Lists For Dropdowns On a Hidden Sheet.