Method 1: Named Ranges
Perhaps the easiest and quickest way to overcome Excel's data-validation barrier is by naming the range where the list resides. To create a named range, select the cells containing the list and enter a name in the Name box that appears at the left end of the Formula bar. For the purposes of this example, we will assume your range is called MyRange.
Select the cell in which you want the drop-down list to appear and then select Data » Validation. Select List from the Allow: field, and in the Source: box enter
=MyRange. Click OK.
Because you used the named range, your list (even though it resides on another worksheet) now can 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 then can use that cell as a local cell reference, even though it gets its data from another worksheet. You can use this feature to reference the worksheet where your list resides.
Assume your list resides on Sheet1 in the range $A$1:$A$8. Click any cell on a different worksheet where you want to have this validation list (pick list) appear. Then select Data » Validation and choose List from the Allow: field. In the Source: box, enter the following code:
Ensure that the In-Cell drop-down box is checked and click OK. The list that resides on Sheet1 should appear in your drop-down validation list.
If the name of the worksheet on which your list resides contains spaces, you need to use the
INDIRECT function in the following way:
The difference here is that you used a single apostrophe immediately after the first quotation mark and another single apostrophe immediately before the exclamation point.
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 Both Methods
Named ranges and the
INDIRECT function each have an advantage and a disadvantage.
The advantage to using a named range is that 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 change 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/row or last cell/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 a named range, those changes will not affect the validation list.