Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
Hi everyone,
I'm kind of in a bind here, and I'm trying to figure out how to do this in Excel. Basically, what I want to do, is I want to create a drop down list that provides Text, but assignes the text a numiercal value to be used in another formula. Example. The drop down list contains the following in A1 and in A2: Excellent Poor Terrible I need Excellent to have a value of 2, Poor to have a value of 1, and Terrible to have a value of 0 So in Cell A3, I can have the formula =A1*A2 and it will give me a numerical value. Can this be done? If so how, cause I am stumpted... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
Hi!
Try one of these: =IF(OR(A1="",A2=""),"",(MATCH(A1,{"Terrible","Poor ","Excellent"},0)-1)*(MATCH(A2,{"Terrible","Poor","Excellent"},0)-1)) =IF(OR(A1="",A2=""),"",VLOOKUP(A1,{"Terrible",0;"P oor",1;"Excellent",2},2,0)*VLOOKUP(A2,{"Terrible", 0;"Poor",1;"Excellent",2},2,0)) Biff "EyeNoNothing" <u18483@uwe wrote in message news:5b8111f649bcb@uwe... Hi everyone, I'm kind of in a bind here, and I'm trying to figure out how to do this in Excel. Basically, what I want to do, is I want to create a drop down list that provides Text, but assignes the text a numiercal value to be used in another formula. Example. The drop down list contains the following in A1 and in A2: Excellent Poor Terrible I need Excellent to have a value of 2, Poor to have a value of 1, and Terrible to have a value of 0 So in Cell A3, I can have the formula =A1*A2 and it will give me a numerical value. Can this be done? If so how, cause I am stumpted... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
Here's a couple of ways.
1- Type your list in an out of the way part of your worksheet, then place a Combo Box from the "Forms" toolbar on top of A1 (the combo box will not be in the cell but rather float above it), right click on the combo box and select "Format Control" next click on the "Control" tab, in the "Input Range" box type the range of your list and in the "Cell link" box type A1. Now each entry will be assigned a number from 1 to how many entry you have.....excellent = 1, poor = 2, terrible = 3 so to have "excellent" = 2 you would use something like =(3-A1)*(3-A2), repeat for A2. 2- Select A1 and A2 then go to Data/Validation next select "list" from the "Allow" menu and in the formula box type your list seperated by comas, hit Ok when done. In A3 type: =LOOKUP(A1,{"excellent","poor","terrible"},{2,1,0} )*LOOKUP(A2,{"excellent","poor","terrible"},{2,1,0 }) HTH JG "EyeNoNothing" wrote: Hi everyone, I'm kind of in a bind here, and I'm trying to figure out how to do this in Excel. Basically, what I want to do, is I want to create a drop down list that provides Text, but assignes the text a numiercal value to be used in another formula. Example. The drop down list contains the following in A1 and in A2: Excellent Poor Terrible I need Excellent to have a value of 2, Poor to have a value of 1, and Terrible to have a value of 0 So in Cell A3, I can have the formula =A1*A2 and it will give me a numerical value. Can this be done? If so how, cause I am stumpted... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
similar to pinmaster,create a drop down list using combo box from forms,but
in another cell point to the linked cell say c6 and =c6-1,so choice 1of terrible will be 0 etc.In another cell if you need the terrible etc to be shown =choose(linked cell,cell ref1,cellref2,cellref3).Cellref1,2,and 3 is the three cells you have typed poor, terrible,excellent -- paul remove nospam for email addy! "pinmaster" wrote: Here's a couple of ways. 1- Type your list in an out of the way part of your worksheet, then place a Combo Box from the "Forms" toolbar on top of A1 (the combo box will not be in the cell but rather float above it), right click on the combo box and select "Format Control" next click on the "Control" tab, in the "Input Range" box type the range of your list and in the "Cell link" box type A1. Now each entry will be assigned a number from 1 to how many entry you have.....excellent = 1, poor = 2, terrible = 3 so to have "excellent" = 2 you would use something like =(3-A1)*(3-A2), repeat for A2. 2- Select A1 and A2 then go to Data/Validation next select "list" from the "Allow" menu and in the formula box type your list seperated by comas, hit Ok when done. In A3 type: =LOOKUP(A1,{"excellent","poor","terrible"},{2,1,0} )*LOOKUP(A2,{"excellent","poor","terrible"},{2,1,0 }) HTH JG "EyeNoNothing" wrote: Hi everyone, I'm kind of in a bind here, and I'm trying to figure out how to do this in Excel. Basically, what I want to do, is I want to create a drop down list that provides Text, but assignes the text a numiercal value to be used in another formula. Example. The drop down list contains the following in A1 and in A2: Excellent Poor Terrible I need Excellent to have a value of 2, Poor to have a value of 1, and Terrible to have a value of 0 So in Cell A3, I can have the formula =A1*A2 and it will give me a numerical value. Can this be done? If so how, cause I am stumpted... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique drop down list | Excel Worksheet Functions | |||
re-ordering the list on a filter drop down | Excel Discussion (Misc queries) | |||
Using symbols for drop down list boxes? | Excel Worksheet Functions | |||
drop down list multiple columns | Excel Discussion (Misc queries) | |||
formatting drop down list | Excel Discussion (Misc queries) |