Create a legend of values
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/...soft_excel.htm http://www.officearticles.com/excel/...soft_excel.htm Walks you through a vlookup: http://www.officearticles.com/tutori...excel.htm#boss ******************* ~Anne Troy www.OfficeArticles.com wrote in message oups.com... 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. |
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 .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- wrote in message oups.com... 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. |
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. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
All times are GMT +1. The time now is 04:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com