Using a Data Validation Source List from a Separate Workbook_1441

Using a Data Validation Source List from a Separate Workbook Overview

Excel's Data Validation feature makes it so simple to create custom drop-down lists in your worksheet. If you already have the data you need for your list in another workbook and do not wish to reinvent the wheel, you can save some steps by linking to a list in another workbook.

Save Time by Linking

While you can create an Excel drop down using worksheet or workbook Dell Vostro 1500 Battery, 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 Dell Inspiron 1420 Battery, 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 Hp Pavilion Dv6 Battery, 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.