Data Validation

November 16, 2001 - by Bill Jelen

Gareth from England writes and asks about having a drop-down list on one worksheet where the list of valid cells is on another worksheet. Gareth also notes that he would prefer not to use activeX combo boxes on the sheet, as they do not move and resize with the cells.

The answer to this problem is using Data Validation. This feature, introduced in Excel 97 offers a simple way to have a drop down list right in the cell. There is no problem with moving or resizing.

The simple case of setting up Data Validation is easy. Go to an out-of-the way place on your worksheet and enter the list of values. Go to the cell where you want the dropdown to appear. From the menu, select Data, Validation. In the allow box, choose List. In the list box, use the RefEdit (red and white icon at the right) and select your range. That is all there is to setting up a data validation.

Here is the real tip for today: Generally, when you try to set up a data validation and you enter a list that is on another sheet, for example if you enter =Sheet2!A1:A99, you will get an error saying that "You may not use references to other worksheets for data validation criteria". There is a way around this limitation! Go to Sheet2, highlight your list range and give it a name with Insert - Names - Define. If you assign A1:A99 on Sheet2 the range name of MyList (or anything you can remember). Then go to your original sheet, and in the Data Validation dialog, for the List, enter =MyList and you will be able to have the validations on another worksheet.