Drop-down menu in Excel cells

WLM21

I have an old version of Excel ... 2002, but it works fine.

I want to add a drop-down menu option to a series of cells, which I now know is done using the validation option.

However, if I later want to type in something else, this is not possible.

Say, 99% of the time I enter option A,B,C or D in row 1 of my spreadsheet, but then on the odd occasion I find out I need to enter EFGH. How to I go about this ?

What happens then, if I need J K L M N or anything else ?

Is the only way to do this by adding a few 'blank cells' in the list of drop down option ?

thank you

Le_Kirk

Just type what you want in the cell

WLM21

Le_Kirk wrote: »

Just type what you want in the cell

That doesn't work .. an error message pops up which states ..

The value you entered is not valid

A user has restricted values that can be entered in this cell

Tigsteroonie

I'm pretty sure that in the options when you set validation, you can choose whether to restrict it to the LOV or accept something typed. Unfortunately I don't have Excel here to tell you what that setting is!! I just know that in one column of a template I designed (in Excel 97) I used this setting so users had the LOV as a suggestion but could type in other info.

Think I've found something by googling - try in the Alerts section of the validation. You may have a 'stop' alert set so that the user can only select something from the LOV; you want a 'warning' alert that informs the user it is wrong but still allows entry.

grumbler

Le_Kirk wrote: »

Just type what you want in the cell

For this Error Alert has to be set to either 'Warning' or 'Information', not 'Stop' (2007, not sure about 2002).

lovinituk

Turn the error alert off - http://www.excelforum.com/excel-general/655673-restricted-values.html

WLM21

Thanks everybody ... all sorted now

Just needed to turn off the alert, as mentioned by Tigsteroonie

Le_Kirk

WLM21 wrote: »

Thanks everybody ... all sorted now

Just needed to turn off the alert, as mentioned by Tigsteroonie

Thanks Tigsteroonie, you beat me to it. OP You can set the alert to say anything you want as I am sure you have now discovered.