We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Drop-down menu in Excel cells

Options
WLM21
WLM21 Posts: 1,603 Forumite
Part of the Furniture 500 Posts Combo Breaker
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

Comments

  • Le_Kirk
    Le_Kirk Posts: 24,511 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    Just type what you want in the cell
  • WLM21
    WLM21 Posts: 1,603 Forumite
    Part of the Furniture 500 Posts Combo Breaker
    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
    Tigsteroonie Posts: 24,954 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    edited 22 December 2015 at 6:35PM
    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.
    :heartpuls Mrs Marleyboy :heartpuls

    MSE: many of the benefits of a helpful family, without disadvantages like having to compete for the tv remote

    :) Proud Parents to an Aut-some son :)
  • grumbler
    grumbler Posts: 58,629 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    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).
  • WLM21
    WLM21 Posts: 1,603 Forumite
    Part of the Furniture 500 Posts Combo Breaker
    Thanks everybody ... all sorted now

    Just needed to turn off the alert, as mentioned by Tigsteroonie
  • Le_Kirk
    Le_Kirk Posts: 24,511 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    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.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.9K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.9K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.1K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.