5. 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.
Figure 1346 Optionally, provide a ToolTip with a note.
6. Click OK to apply the validation.
7. 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.
Figure 1347 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.
Starting in Excel 2007, you can stores lists on a second worksheet. In Excel 2003 and earlier, you would have to name the list range and use the range name as the list source.