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!
setting cell value = to drop down menu value
-
-
-
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!
-
I've attached a quick example. List and Pick are defined names and don't have to be on the same sheet.
-
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.
-
alright this lookseasy enough thanks alot to everyone who replied!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!