Using an Excel Validation List in Another Worksheet or Workbook 

Data validation is a Microsoft Excel feature that helps users enter data correctly on worksheets. One form of data validation lets you define a source list, elsewhere on the worksheet, of the possible entries for a cell. When a user clicks the cell to enter data, Excel provides a drop-down list of the possible entries.

With data validation, users can click a choice from a list you supply, ensuring that only appropriate values are entered.

Create the source list  When you set up data validation rules for a cell where you want users to select from your list, you first type the list itself (the source list) in a separate range of cells. Excel expects the source list to be on the same worksheet as the cell where you're validating the data.

Control and update the source list  If you don't want users of your validated worksheet to see or change the actual source list, you have to hide the column that the source list is in, and you have to protect the worksheet. If you maintain a master source list in a different workbook, copying that list to other workbooks for validation can be inconvenient, especially if your master list changes frequently and would have to be recopied.

Use names in validation rules  Fortunately, there's an easy way to refer to a source list on another worksheet or in another workbook. In the validation workbook, you define a name that refers to the list. A name is a word or label that you assign to represent a specific range of cells. You can define a name in one workbook that refers to a range of cells in a different workbook. Once the name has been defined, you use the name when you set up the validation rules.

How to set up validation from a list on another worksheet or workbook

1. Open the workbook that contains the source list. For example, the list used for validation in Inventory.xls might be on Sheet1 of another workbook, MasterList.xls:
   
2. If you're doing the data validation in a separate workbook, open that workbook too. For example, open Inventory.xls.
3. Click the worksheet where you want to validate the data.
4. On the Insert menu, point to Name, and then click Define.
5. In the Names in workbook box, type a name to refer to your source list. For the list illustrated in step 1, you might use StatusList.
6. Click in the Refers to box, and delete any information in the box.
7. On the Window menu, click the name of the workbook that contains your source list (if different from the current workbook), and then click the sheet tab of the sheet containing the source list. For the example reorder status source list, click MasterList.xls on the Window menu, and then click Sheet1.
8. Select the cells in your list. Excel temporarily shrinks the Define Name dialog box and fills in the Refers to box for you with a reference to the workbook, sheet, and range (or the sheet and range, if the range is in the same workbook):
   
9. Click OK in the Define Name dialog box. Excel returns to the workbook and worksheet where you're validating the data, and defines the name in that workbook. In the example, the name StatusList is defined as part of Inventory.xls, referring to the source list in MasterList.xls.
10. Now you can set up the validation rules. Click a cell where you want to validate the data. For example, you might click cell D3 on the Imports worksheet:
    
11. On the Data menu, click Validation, and then click the Settings tab.
12. In the Allow box, click List.
13. In the Source box, type an equal sign (=) followed by the name you defined in steps 4 through 9 (for example, =StatusList). When you click OK, the drop-down arrow appears beside the cell, and you can click it to choose an item from the list.

Once you've set up validation for one cell, you can use the Copy button or the fill handle to copy the validation rule to other cells.

For more information about using names for ranges, type defining names in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. For more information about validating data, type data validation in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Making the Two-Workbook Approach Easier to Use

Make the source list workbook available to all  To use the validation rule, both the workbook you're validating and the workbook containing the source list must be open at the same time. If several users will need to open the source list workbook simultaneously, users can open the workbook read-only. You can make this easy by setting read-only recommended when you save the source list workbook. For more information about saving read-only worksheets, type read-only worksheet in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

Hide the source list workbook  If you don't want users to see or change the source list workbook, you can hide the workbook. For more information about hiding a workbook, type hide a workbook in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.