Calculate Value on new cell based on List
 

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

Calculate Value on new cell based on List
 

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" <Ranjit S wrote in message
...
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

Calculate Value on new cell based on List
 

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

This worked like a charm !!

Thanks once again!!

Warm Regards,
Hans



"John" wrote:

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" <Ranjit S wrote in message
...
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

Calculate Value on new cell based on List
 

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

"Ranjit S Hans" wrote:

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

This worked like a charm !!

Thanks once again!!

Warm Regards,
Hans



"John" wrote:

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" <Ranjit S wrote in message
...
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

Calculate Value on new cell based on List
 

You're Welcome
John
"Ranjit S Hans" wrote in message
...
Thanks so much for the quick review and help John..

This worked like a charm !!

Thanks once again!!

Warm Regards,
Hans



"John" wrote:

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" <Ranjit S wrote in
message
...
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

Calculate Value on new cell based on List
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Ranjit S Hans" wrote:

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