Force Data Validation to Reference a List on Another Worksheet

One of the options available in the data validation feature is the List option, which provides a nice drop-down list of specific items from which the user can choose. One drawback with data validation is that the moment you try to reference a list that resides on another worksheet, you will be told this is not possible. Luckily, you can make it possible by using this tutorial.

You can force data validation to reference a list on another worksheet using two different approaches: named ranges and the INDIRECT function.

Method 1: Named Ranges

Perhaps the easiest and quickest way to perform this task is by naming the range where the list resides. For this purposes of this exercise, we will assume you called that range MyRange. Select the cell in which you want this drop-down list to appear and select Data Validation. Select List from the Allow: field, and in the Source: box, enter =MyRange. Click OK. Your list (which resides on another worksheet) can now be used for the validation list.

Method 2: the INDIRECT Function

The INDIRECT function enables you to reference a cell containing text that represents a cell address. You can use the cell containing the INDIRECT function as the cell reference, and you can use this feature to reference the worksheet where the list resides.

Assume your list resides on Sheet1 in the range $A$1:$A$10. Click in any cell on another worksheet where you want to have this validation list (pick list) appear. Then select Data Validation, and select List from the Allow: field. In the Source: box, enter the following function:

=INDIRECT("Sheet1!$A$1:$A$10")

Ensure that the In-Cell drop-down box is checked and click OK. The list that resides on Sheet1 should be in your drop-down validation list.

If the name of the worksheet on which the list resides contains spaces, use the INDIRECT function as follows:

=INDIRECT("'Sheet 1'!$A$1:$A$10")

Here you used a single apostrophe immediately after the first quotation mark and another single apostrophe immediately before the exclamation point. The apostrophes identify the boundaries of the sheet name to Excel.

It is a good idea to always use the single apostrophe, regardless of whether your sheet name contains spaces. You still will be able to reference a sheet with no spaces in its name, and it makes it easier to make changes later.

The Pros and Cons of Each Method

There are advantages and disadvantages to using named ranges and the INDIRECT function to force data validation to reference a list on another worksheet.

The advantage to using a named range in this scenario is that any changes you make to the sheet name will have no effect on the validation list. This highlights the INDIRECT function's disadvantage-namely, that any changes you make to the sheet name will not be reflected automatically within the INDIRECT function, so you will have to manually change the function to correspond to the new sheet name.

The advantage to using the INDIRECT function is that if the first cell or row or last cell or row is deleted from the named range, the named range will return a #REF! error. This highlights the disadvantage to using named ranges: if you delete any cells or rows from within the named range, those changes will not affect the validation list.