Assign values to names in a drop-down list?

Is it possible to assign values to names in a list, so that when you validate
it as a drop-down list, you can select a name from the drop-down and it's
corresponding value will be added to separate cell? Basically, I have survey
questions which have five possible responses: Excellent - Very Good - Good -
Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very
Good and so on, so that when a response is selected from a drop-down, it's
value appears in a separate cell (so that we can calculate a total and
average score from the selections).

Thanks
B

Hi Barry

a couple of choices,

1)
if you only have 5 options you can use an IF function - assuming the drop
down is in A1 the formula in B1 would be
=IF(A1="","",IF(A1="Excellent",5,IF(A1="Very
Good",4,IF(A1="Good",3,IF(A1="Fair",2,IF(A1="Poor",1,"Check Entry"))))))

2)
or create a table somewhere in your workbook (say sheet2 cells A2:B6) as
follows
.........A.................B
1....Grade.........Value
2.....Excellent.......5
3.....Very Good....4
4.....Good.............3
5......Fair...............2
6.....Poor...............1

and use a VLOOKUP function in cell B1
=VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)

- to trap for errors nest it in an IF(ISNA( function, e.g.
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)),"",VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0))

3)
use the following formula in B1
=IF(ISNA(VLOOKUP(A1,{"Excellent",5;"Very
Good",4;"Good",3;"Fair",2;"Poor",1},2,0)),"",VLOOKUP(A1,{"Excellent",5;"Very
Good",4;"Good",3;"Fair",2;"Poor",1},2,0))


Hope this helps
Cheers
JulieD


"Barry L" <Barry [email protected]> wrote in message
news:[email protected]...
> Is it possible to assign values to names in a list, so that when you
> validate
> it as a drop-down list, you can select a name from the drop-down and it's
> corresponding value will be added to separate cell? Basically, I have
> survey
> questions which have five possible responses: Excellent - Very Good -
> Good -
> Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for
> Very
> Good and so on, so that when a response is selected from a drop-down, it's
> value appears in a separate cell (so that we can calculate a total and
> average score from the selections).
>
> Thanks
> B

Great thanks Julie!

Barry

"JulieD" wrote:

> Hi Barry
>
> a couple of choices,
>
> 1)
> if you only have 5 options you can use an IF function - assuming the drop
> down is in A1 the formula in B1 would be
> =IF(A1="","",IF(A1="Excellent",5,IF(A1="Very
> Good",4,IF(A1="Good",3,IF(A1="Fair",2,IF(A1="Poor",1,"Check Entry"))))))
>
> 2)
> or create a table somewhere in your workbook (say sheet2 cells A2:B6) as
> follows
> .........A.................B
> 1....Grade.........Value
> 2.....Excellent.......5
> 3.....Very Good....4
> 4.....Good.............3
> 5......Fair...............2
> 6.....Poor...............1
>
> and use a VLOOKUP function in cell B1
> =VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)
>
> - to trap for errors nest it in an IF(ISNA( function, e.g.
> =IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)),"",VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0))
>
> 3)
> use the following formula in B1
> =IF(ISNA(VLOOKUP(A1,{"Excellent",5;"Very
> Good",4;"Good",3;"Fair",2;"Poor",1},2,0)),"",VLOOKUP(A1,{"Excellent",5;"Very
> Good",4;"Good",3;"Fair",2;"Poor",1},2,0))
>
>
> Hope this helps
> Cheers
> JulieD
>
>
> "Barry L" <Barry [email protected]> wrote in message
> news:[email protected]...
> > Is it possible to assign values to names in a list, so that when you
> > validate
> > it as a drop-down list, you can select a name from the drop-down and it's
> > corresponding value will be added to separate cell? Basically, I have
> > survey
> > questions which have five possible responses: Excellent - Very Good -
> > Good -
> > Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for
> > Very
> > Good and so on, so that when a response is selected from a drop-down, it's
> > value appears in a separate cell (so that we can calculate a total and
> > average score from the selections).
> >
> > Thanks
> > B
>
>
>

you're welcome

"Barry L" <[email protected]> wrote in message
news:[email protected]...
> Great thanks Julie!
>
> Barry
>
> "JulieD" wrote:
>
>> Hi Barry
>>
>> a couple of choices,
>>
>> 1)
>> if you only have 5 options you can use an IF function - assuming the drop
>> down is in A1 the formula in B1 would be
>> =IF(A1="","",IF(A1="Excellent",5,IF(A1="Very
>> Good",4,IF(A1="Good",3,IF(A1="Fair",2,IF(A1="Poor",1,"Check Entry"))))))
>>
>> 2)
>> or create a table somewhere in your workbook (say sheet2 cells A2:B6) as
>> follows
>> .........A.................B
>> 1....Grade.........Value
>> 2.....Excellent.......5
>> 3.....Very Good....4
>> 4.....Good.............3
>> 5......Fair...............2
>> 6.....Poor...............1
>>
>> and use a VLOOKUP function in cell B1
>> =VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)
>>
>> - to trap for errors nest it in an IF(ISNA( function, e.g.
>> =IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)),"",VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0))
>>
>> 3)
>> use the following formula in B1
>> =IF(ISNA(VLOOKUP(A1,{"Excellent",5;"Very
>> Good",4;"Good",3;"Fair",2;"Poor",1},2,0)),"",VLOOKUP(A1,{"Excellent",5;"Very
>> Good",4;"Good",3;"Fair",2;"Poor",1},2,0))
>>
>>
>> Hope this helps
>> Cheers
>> JulieD
>>
>>
>> "Barry L" <Barry [email protected]> wrote in message
>> news:[email protected]...
>> > Is it possible to assign values to names in a list, so that when you
>> > validate
>> > it as a drop-down list, you can select a name from the drop-down and
>> > it's
>> > corresponding value will be added to separate cell? Basically, I have
>> > survey
>> > questions which have five possible responses: Excellent - Very Good -
>> > Good -
>> > Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for
>> > Very
>> > Good and so on, so that when a response is selected from a drop-down,
>> > it's
>> > value appears in a separate cell (so that we can calculate a total and
>> > average score from the selections).
>> >
>> > Thanks
>> > B
>>
>>
>>

Hi...I'm new to this! and I'm

I'm running a Fantasy Formula 1 competition and have transfered the information over to Excel 2007 (Acer Laptop)

The problem I have is to assign values to the drivers in a drop down cell?

In cell A2:A27 are the names
In cell B2:B27 are the values

In cells G2,I2 and K2 are the drivers drop down list
I require the values of each driver to appear in cells H2,J2 and L2
each drive has a diffrent price tag.

Many many thanks

I have tried to follow these recommendations but I keep receiving a error message saying a parenthesis is missing somewhere. Please help.