Written by: Senior Instructor
8/20/2007 12:35 PM

Inserting a drop-down list to a cell is a slick way to add a touch of professionalism to your worksheets – and also ensure that erroneous data is not entered into the cell. Best of all, macros are not required. For a simple example, let’s say you have an input cell in which the user is supposed to enter a month name: January, February, and so on.

• Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, we’ll assume the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet.
• Select the cell that is to contain the drop-down list. If you’d like more than one cell to display the same list, select them all now rather than setting them up one at a time.
• Choose Data/Validation to display the Data Validation dialog box.
• Click the Settings tab.
• In the Allow field, select List.
• In the Source field, specify the range that contains the list items. In this example, type =E1:E12 into the field. If you have named the range that contains the values, then you can type an equal sign followed by the range name. Using a named range could also allow you to use a value list from another sheet in the workbook.
• Click OK.
• After performing these steps, you’ll see a drop-down arrow whenever any of the drop-down cells you created is “active” (that is, selected and awaiting input.) If you try to enter something else in the cell, you’ll be scolded in the form of a pop-up message.
• If you’d like to provide your own wording for the error message, or if you want to provide input instructions, visit the other two tabs in the Data Validation dialog box.

This tip is from MicroAssist Instructor Andy Weaver

• If your list is relatively small, you can bypass the first step above and enter the list items directly into the Source field. Just separate each list item with a comma.

Trackback Print

Location: Blogs Tips, Trips & Traps