You can force data validation to reference a list on another worksheet using two different approaches: named ranges and the
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
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:
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:
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.