Using a validation list in another worksheet or workbook

Applies to
Microsoft Excel 2000

Data validation is an 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.

Demo of data validation

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.

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:

Example of a data validation list

  1. If you're doing the data validation in a separate workbook, open that workbook too. For example, open Inventory.xls.
  2. Click the worksheet where you want to validate the data.
  3. On the Insert menu, point to Name, and then click Name.
  4. 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.
  5. Click in the Refers to box, and delete any information in the box.
  6. 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.
  7. 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):

Selecting the validation list

  1. 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.
  2. 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:

Clicking the cell where you want to validate data

  1. On the Data menu, click Validation, and then click the Settings tab.
  2. In the Allow box , click List.
  3. 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.

Open the source list workbook automatically    Once you hide the source list workbook, you can record a macro to open it automatically whenever the workbook with the validation rules is opened. For more information about creating and using macros, type recording macros in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.