Problem: I need my sales managers to select a product from our company's product line. All the pricing lookups in the worksheet rely on the product being entered correctly. I find that if I allow my managers to type an entry, they will find too many ways to misspell items. For example, where I may be expecting PDT-960, they are likely to enter PDTÂ 960, 960, and many other variations. If I could offer them a list to select from, they would automatically select the correct spelling of the product.
Strategy: You can easily allow managers to select from a list by using the Data Validation command. It turns out that every cell has a data validation setting to allow any value. You can change this default setting:
- In an out-of-the-way section of the worksheet, type a valid list of values.
- Select a cell where the person will be entering data and choose Data, Data Validation.
- Choose the Allow dropdown and change Any Value to List. The check box for In-Cell Dropdown appears and is automatically checked.
- Point to the range in the Source field. Alternatively, if the list is short, you can skip step 1 and type the list items, separated by commas, in this box. This particular worksheet already has the valid products as the first column of a lookup table used to get prices.
- Specify the location for the list.
- Optionally, use the Input Message tab of the Data Validation dialog to provide instructions to the sales managers. You can also use the Error Alert tab to display custom text when the sales managers do not select from your list.
- ÂOptionally, provide a ToolTip with a note.
- Click OK to apply the validation.
- When someone selects the cell, a dropdown will appear, along with your input message. Choose the dropdown arrow, and the managers will be able to select from a list of products.
- Choose from the list.
Additional Details: After you have set up the validation in one cell, you can copy it to other cells. You select the cell and press Ctrl+C to copy. Then you select cells B7:B20 and select Home, Paste dropdown, Paste Special, Validation.
Gotcha: I am always on the lookout for sales managers who know just a little too much about Excel. If a manager were smart enough to delete row 5, he could also delete row 5 of the lookup table off to the right. If you store your list on a hidden sheet with a range name, you can prevent this.
Gotcha: If someone copies a bunch of cells and pastes them over your validated cells in B, the validation will not work. Anyone can get an invalid value in a cell by using Copy and Paste.