See all Microsoft Excel tips
This forces you to keep your validation lists in an out-of-the way section of a current worksheet. No matter where you hide the list, someone manages to find a way to inadvertently delete items from it.
Strategy: There seems to be one workaround that works, at least up through Excel 2003. Follow these steps.
1) Insert a blank worksheet in the workbook. Type your list on this worksheet.
2) Highlight the list. Click in the Name box to the left of the formula bar. Type a name, such as ItemList, and hit Enter, as shown in Fig. 1490.
3) This action
sets up a workbook-level named range. Hide the new worksheet with Format – Sheet – Hide.
4) On the original worksheet, select a cell. From the menu, select Data – Validation. Change the Allow box to List. In the Source box, type an Equal sign and the name of your range, as shown in Fig. 1491. Choose OK.
Result: The cell will have validation based on a range on another worksheet.
Summary: Although you cannot officially have validation lists on another sheet, by using a workbook-level named range, there is a workaround.
Commands Discussed: Data – Validation
See all Microsoft Excel tips