Ask Mr. Excel - Data Validation dropdowns disappear after freeze panes

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Past Tip of the Week

Caroline from the U.K. came up with this problem. I have a forecasting worksheet that has been in use for several weeks without a problem. The worksheet has Data Validations with dropdown boxes in columns C & D. I changed some fonts and repositioned the frozen window so that the user can see columns A:E all the time. Now my 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.

MrExcel.com Consulting can be hired to implement this concept with your data.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.