Method 2: the INDIRECT Function

The 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, under the Data tab, select Data → Data Validation (pre-2007, Data → Validation). Choose List from the Allow: field. In the Source: box, enter the following code:

Ensure that the In-Cell drop-down checkbox is selected 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 type 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 will still 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 of 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.