Assign values to names in a drop-down list?

G

Guest

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
 
J

JulieD

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
 
G

Guest

Great thanks Julie!

Barry

JulieD said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtered drop down list 2
Drop Down Lists - Shrinking the List 5
Selecting multiple values from a drop down list box 5
Assign numerical value to cell based on selection from drop-down m 1
Adding Values To Drop Down Menu 1
Change drop down list in one cell based on values in other cells 2
Conditional Drop Down List 1
drop down list should remember selection. 6

Top