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.
This is a great feature to know. I am embarrassed that after 50+ tips of the week, I have never fully explained this feature. That will come soon. For now, you can review these two tips that touch on the issue:
http://www.mrexcel.com/tip046.shtml touches on the issue, although without any screen shots.
http://www.mrexcel.com/tip008.shtml covers a more complex example of Data Validation.
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.
Finally, I have a favor to ask: This morning, I surfed to MrExcel.com in order to find the links above, and I was greeted with an annoying popup advertising window that opened in a new browser. My agreement with Burst Media prohibits these pop-up windows. They are annoying, rude and I don't want them on the site. Please, if you are a regular reader of MrExcel and you ever notice a pop-up window coming from this site, please immediately send a message to Bill@MrExcel.com so that I can take issue with the ad company. I am off to do that right now. Thanks for your help, and sorry for the annoying pop-ups.
By Bill Jelen on 16-Nov-2001