Calculate Value on new cell based on List

[Ranjit S Hans]

Hi Excel Gurus,

How do I calculate a value on a new cell based on what the user selects from
a List.

For Example :

If I have a dropdown List of 5 items -> APPLE, ORANGE, BANANA, GRAPES,
PINEAPPLE

Selecting APPLE should paste a value 1 in the adjoining cell
Selecting ORANGE should paste a value 2 in the adjoining cell
Selecting BANANA should paste a value 3 in the adjoining cell
Selecting GRAPES should paste a value 4 in the adjoining cell
Selecting PINEAPPLE should paste a value 5 in the adjoining cell

Thanks for your time.

Warm Regards,
Ranjit S Hans

 

[John]

Hi
You can do it this way:
=IF(A1="Apple",1,IF(A1="Orange",2,IF(A1="Pineapple",3,IF(A1="banana",4,""))))
etc,,etc..
Change A1 for the cell that list the items.
HTH
John

 

[Ranjit S Hans]

Thanks so much for the quick review and help John..

This worked like a charm !!

Thanks once again!!

Warm Regards,
Hans

 

[Dave Curtis]

Hi,

Which list box are you using?
If you use the one from the Control Toolbox, the linked cell will display
the item selected from the list, ie GRAPES.
If you use the listbox from the Forms toolbar, the linked cell will display
the position of the item in the list, ie if you select GRAPES, it displays 4.

Dave

 

[John]

You're Welcome
John

Thanks so much for the quick review and help John..

This worked like a charm !!

Thanks once again!!

Warm Regards,
Hans

 

[Shane Devenshire]

Hi,

In the long run a safer approach would be either VLOOKUP with a little
lookup table or

=MATCH(A1,{"Apple","Orange","Pineapple","Banana"},0)

=VLOOKUP(A1,D1:E4,2,)

Where D1:E4 represent a range like this
Apple 1
Orange 2
Pineapple 3
Banana 4

Reasons:
1. They are shorter
2. If your list exceeds 7 items in 2003 you won't be able to use the IF
approach.
3. A lot easier to write and maintain, particularly the VLOOKUP option.