Create a legend of values

  • Thread starter Thread starter keithcp
  • Start date Start date
K

keithcp

Hello,
Can anyone tell me how I can assign a value to a word from a dropdown
list in excel?
In other words if I choose an item from the dropdown list, how can I
assign a static value to it?
An example would be if I select the item "apple" from the list, it
would automatically assign the number 5 to it that I can use in a
formula.
Thanks in advance.
 
What if they choose Oranges, and where are these static values stored? If
you have 7 or less, you can use an IF statement, but if you have more than
that, you're best to create a table somewhere and create a vlookup.
http://www.officearticles.com/excel/if_statements_in_formulas_in_microsoft_excel.htm
http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm
Walks you through a vlookup:
http://www.officearticles.com/tutorials/an_extreme_tutorial_in_microsoft_excel.htm#boss

*******************
~Anne Troy

www.OfficeArticles.com
 
Think you could also consider using a combo box (drawn from the Forms
toolbar) with its linked cell functionality returning the numbers that you
seek

Try this simple experiment ..

Put in A1: Text1
Fill A1 down to A20
This rapidly creates a list of 20 items: Text1, Text2 ... Text20

From the Forms* toolbar, click on Combo box icon,
then draw a combo box somewhere on the sheet
(*activate via: View > Toolbars > Forms, if necess.)

Right-click on the combo box > Format Control

In the Control tab, make the settings:
Input range: $A$1:$A$20
Cell link: $B$1
Click OK

Test it out!

Select from the combo box droplist: Text1
The linked cell, i.e. B1 will show: 1
(since Text1 is the 1st item in the list)

Select Text 10, B1 will return: 10
Select Text 19, B1 will return: 19
And so on ..
 
Note that the combo box, the input range, and linked cell can be on 3
separate sheets, providing flexibility in setting it up to suit what you
have in mind.

For example:

In the Control tab, with the settings:
Input range: Sheet1!$A$1:$A$20
Cell link: Sheet3!$B$1

The combo box itself could be somewhere in Sheet2, reading from the source
range in Sheet1!$A$1:$A$20, and outputting the droplist selections in
Sheet3!$B$1 (the linked cell). Named ranges can also be used for the input
range and the cell link.
 
Back
Top