Drop Down List Question

[EyeNoNothing]

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...

 

[Biff]

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;"Poor",1;"Excellent",2},2,0)*VLOOKUP(A2,{"Terrible",0;"Poor",1;"Excellent",2},2,0))

Biff

 

[Guest]

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

 

[Guest]

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