assign numerical values to text
hey i need to make it possible to take text from 3 different drop down boxes, assign them a numerical value, then have the values added together and displayed in a different cell.
for example if from the drop down box in cell a1 was the word 'dog', and the text in b1 was 'cat' and the text in c1 was 'goat' then i need dog to be set equal to 6, cat set equal to 3, and goat set equal to 1. THEN have those values added together and be placed in cell d1.any help would be great.thanks a b c d 1 dog cat goat 10 2 3 4 5 
ptrussell2009,
Welcome to the board. We would need to see all the words in each drop down list for column A, B, and C, and their corresponding values. Have a great day, Stan stanleydgromjr Windows Vista Business and Excel 2003, 2007. 
I actually have figured it out, BUT i only know how to do it one cell at a time. These are the objects in the drop down list and their corresponding values:
Environment Impact Cycle xDE=1 1=1 ITC1=1 xDV=2 2=2 ITC2=2 xO1=3 3=3 PR1=3 xOQ=4 4=4 PR2=4 xOV=5 5=5 xR1=6 xRB=7 xRP=8 xSB=9 xTO=10 xTS=11 Here is how I have been doing it one at a time(Im not worried about adding the Cycle field right now thats why its not in the code): Sub Priority() Dim temp As Integer With ActiveSheet If .Range("H9") = ("xDE") Then temp = 1 End If If .Range("H9") = ("xDV") Then temp = 2 End If If .Range("H9") = ("xO1") Then temp = 3 End If If .Range("H9") = ("xOQ") Then temp = 4 End If If .Range("H9") = ("xOV") Then temp = 5 End If If .Range("H9") = ("xR1") Then temp = 6 End If If .Range("H9") = ("xRB") Then temp = 7 End If If .Range("H9") = ("xRP") Then temp = 8 End If If .Range("H9") = ("xSB") Then temp = 9 End If If .Range("H9") = ("xTO") Then .Range("O17") = 10 End If If .Range("H9") = ("xTS") Then temp = 11 End If '............IMPACT.............. If .Range("I9") = ("1") Then temp2 = 1 End If If .Range("I9") = ("2") Then temp2 = 2 End If If .Range("I9") = ("3") Then temp2 = 3 End If If .Range("I9") = ("4") Then temp2 = 4 End If If .Range("I9") = ("5") Then temp2 = 5 End If '......test............. .Range("F9") = temp + temp2 End With End Sub Ive been working on something like this so that I can do it for more than just one cell.I need it to work for about 1000. Last_Row = Range("H1000").End(xlUp).Row For H = 1 To Last_Row If Cells(H, 1) = ("xDE") Then Cells(H, 2) = 1 End If Next H 
ptrussell2009,
What are the actual columns being used to select the drop down lists, and the total column? ? ? ? ? 1 xOQ 2 PR2 10 2 3 4 5 Have a great day, Stan stanleydgromjr Windows Vista Business and Excel 2003, 2007. 
***Dont worry about the Description colum.
F G H I J Priority Description Environment Impact Cycle 1 2 3 4 5 When you click on cell F1 a drop down box comes up with xDE,xDV etc. In the macro i have assigned each one a value of 111(look at my previous post).so when the user selects xDE the value of 1 is stored in a temp variable in the macro but what is displayed on the spreadsheet is xDE. The same goes for Impact and Cycle. The sum of Environment, Impact, and Cycle goes into the Priority column. This is how it should work: Click on cell H1 and choose an Environment for the drop down list. Click on cell I1 and choose an Impact from the drop down list Click on cell J1 and choose a Cycle from the drop down list. Run the macro The value in cell F1 should be the sum of H1+I1+J1 Next I would go to cell H2 and do the same thing all over again. 
ptrussell2009,
This works in my test environment. I added a blank row at the top of each drop down list. Try this formula in cell K1 (then copy it down): =LOOKUP(H1,{0,"xDE","xDV","xO1","xOQ","xOV","xR1", "xRB","xRP","xSB","xTO","xTS"},{0,1,2,3,4,5,6,7,8, 9,10,11})+LOOKUP(I1,{0,1,2,3,4,5},{0,1,2,3,4,5})+L OOKUP(J1,{0,"ITC1","ITC2","PR1","PR2"},{0,1,2,3,4} ) As you change the drop down selections in column H, I, and J, the total will appear in column K, for each row. Have a great day, Stan stanleydgromjr Windows Vista Business and Excel 2003, 2007. 
thanks.it works great.

Change Value to Number and get the sum
Hi,
This is regarding excel data formating, I want to take the values from 10 different fields which will have the values as per the drop down and then sum its correspending rating I found very good solution which has been very useful, however, the field value is not giving me the exact result as per the values in Lookup vector and result vector. For Eg. If im giving :: =LOOKUP(C2,{"","AE","OE","NE","BE"},{0,5,3,1,0}) and if i pick NE from drop down, it takes the value 5 Can you please help. Regards 
It's been awhile since your post, and if you have not figured it out yet you might try something like this:
Sub ranCol() Dim cell As Range For Each cell In Range("A2:A52, B2:B52") If cell.value = "Y" Then cell.value = 2 ElseIf cell.value = "N" Then cell.value = 1 ElseIf cell.value = "" Then cell.value = 0 ElseIf cell.value = "y" Then cell.value = 2 ElseIf cell.value = "n" Then cell.value = 1 ElseIf cell.value = "" Then cell.value = 0 End If Next End Sub 
Alternatively Use a collection or a File
Hi,
I believe a collection with these codes as keys and the numbers as values will work best. Alternatively have the collection reading from a file of keys and value pairs. 
All times are GMT 4. The time now is 09:39 PM. 
Powered by vBulletin®
Copyright ©2000  2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.