MrExcel.com - Your One Stop for Excel Tips & Solutions

Dropdown List By Using Validation

December 25, 2001 - by

Caroline from the U.K. came up with this problem.

I have a forecasting worksheet in Excel that has been in use for several weeks without a problem. The worksheet has Data Validation with dropdown boxes in columns C & D. I changed some fonts and repositioned the frozen window so that the sales rep can see columns A:E all the time. Now my Excel dropdown boxes do not appear at all.

Caroline sent along the workbook and when I opened it up, the Validation boxes appeared on my system. After some research I discovered the problem to be a documented bug in Excel 97.

Anytime that you have a data validation with dropdowns that are in the frozen pane section of the workbook, Excel 97 would not show the dropdown boxes.

The good news is that Microsoft has corrected the issue and if you absolutely need dropdowns in the frozen section of the worksheet, you can upgrade to Excel 2000.

For readers who have not used Data Validation before, here is a quick overview. Caroline is collecting forecasts for 30 product lines. In a hidden row at the top of the worksheet, Caroline entered the valid names for all 30 products in cells A1:AD1. In cell E7, she was collecting a product name. To set up the data validation, she would follow these steps:

  • Put the cellpointer in cell E7.
  • From the menu, choose Data, Validation to bring up the Data Validation dialog box.
  • On the settings tab, change Allow to be "List".
  • In the source, specify A1:AD1. One downside: the list must exist on the same worksheet instead of elsewhere in the workbook.
  • Make sure that in-cell dropdown is selected.
  • Optionally, go to the Input Message and Error Message tabs to provide directions for your worksheet user.

Anytime the cellpointer is in E7, there will be a dropdown box allowing the worksheet user to pick from a list. This will also prevent the user from entering an invalid value.

Caroline's sheet had this validation repeated for each cell from E7:E200. To quickly copy the validation, select E7, Edit - Copy, then select E7:E200 and Edit - PasteSpecial - Validations - OK.