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)),"",VLOO KUP(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)),"",VLOOK UP(A1,{"Excellent",5;"Very
Good",4;"Good",3;"Fair",2;"Poor",1},2,0))


Hope this helps
Cheers
JulieD


"Barry L" <Barry wrote in message
...
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)),"",VLOO KUP(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)),"",VLOOK UP(A1,{"Excellent",5;"Very
Good",4;"Good",3;"Fair",2;"Poor",1},2,0))


Hope this helps
Cheers
JulieD


"Barry L" <Barry wrote in message
...
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" wrote in message
...
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)),"",VLOO KUP(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)),"",VLOOK UP(A1,{"Excellent",5;"Very
Good",4;"Good",3;"Fair",2;"Poor",1},2,0))


Hope this helps
Cheers
JulieD


"Barry L" <Barry wrote in message
...
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