Calculate Value on new cell based on List

  • Thread starter Thread starter Ranjit S Hans
  • Start date Start date
R

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
 
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
 
Thanks so much for the quick review and help John..

This worked like a charm !!

Thanks once again!!

Warm Regards,
Hans
 
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
 
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.
 
Back
Top