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, under the Data tab select Data Tools → Data → Validation (pre-2007, Data → Validation). Select List from the Allow: field, and in the Source: box enter =MyRange. Click OK.

Because you used a named range, your list (even though it resides on another worksheet) can now be used for the validation list.