| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate the number of weeksdays based on a cell value | Excel Worksheet Functions | |||
| IF statement to calculate based on cell values | Excel Worksheet Functions | |||
| auto populate cell based on previous cell drop down list selectio. | Excel Discussion (Misc queries) | |||
| Calculate PivotTable Item Totals Based On Cell Contents? | Excel Worksheet Functions | |||
| Calculate Value of Cell From Pick List Choice | Excel Discussion (Misc queries) | |||
Linear Mode
