Microsoft Excel: Store Lists For Dropdowns On A Hidden Sheet

Problem: As shown in Fig. 1489, the Validation dropdown will not allow you to specify a list on another worksheet.

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

Images