setting cell value = to drop down menu value

  • ok 2 questions actually... I'm new to excel without any training in it whatsoever and I'm wondering... 1.) how do a set a cell to be = to the value selected from a drop down menu... and have the cell display the exact value, ie. "Bob" "Dave" or "Suzy" rather than 1, 2, or 3??? 2.) How can I set excel so that if I Insert a row in somewhere it won't throw off any formulas I already have programmed in below the inserted line? I haven't looked into this one yet, i dunno if it's a valid problem... some guy in my office mentioned he was having trouble with it though... and I'd like to give him a hand... Thanks a ton to anyone who can help me out here!

  • On the menu bar go to Data > Validation > List and set the range for the list to a set of cells in which you have typing the legal entries.


    Note: you might want to "play" with the other-than-list options to see what else Data > Validation can do. Lots of math options also. Excel help may be of use to clarify it also.

  • How have you created your dropdown menus, Data Validation or Combobox from the Control ToolBox? data Validation is probably the easiest as thomach suggests.


    Your question concerning adding rows depends on the formula, Excel usually adjusts formulae as rows are added in most cases. Just experiment with some simple formulae and add rows.

  • I created the drop downs with combo box because with data validation i couldnt get the arrow that lets the user know there is a drop down menu present unless the cell is highlited to appear .. the arrow only stayed visible at all times if i made it combo box style???

  • If you are using Combo boxes then they will return the count of the selected item rather than its value. The easiest way around this is to have an event macro tied to the combobox change event that looks up the entry chosedn and writes it to the designated cell. If you are new to Excel, this may take a bit of trial and error. How to do it will also depend on whether you are using a combo box from the Forms toolbar or from the Controls toolbar.

  • sound slike a pain in the balls! i used the forms toolbox to create it... i'll give ti a shot with the macros .. thank u very much though!

  • Whilst you can use a macro there is absolutely no need to do so.


    The attached sample shows the use of the "Index" function to achieve the result.

    Robert Hind
    Perth - Western Australia

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!