Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list with different values.
Hello!!!
Here is my problem... I have a drop down list and I want each item on it to display a differen value in another cell So if my list is: C2 Value Tom 10 Mary 8 sarah 4 Michael 2 So If I selected Tom from the drop down list it would display 10 in C3 or if I selected mary from the drop down list then it would show 8 in C3. Any ideas if or how I can do this? Thanks so much for your help....I'm always amazed by what people can do on Excel!!! Honey |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list with different values.
Let's say: A2 is your dop down list, and your data in B1:C100
=VLOOKUP(A2,B1:C100,2,0) Adjust to suit "Honey" wrote: Hello!!! Here is my problem... I have a drop down list and I want each item on it to display a differen value in another cell So if my list is: C2 Value Tom 10 Mary 8 sarah 4 Michael 2 So If I selected Tom from the drop down list it would display 10 in C3 or if I selected mary from the drop down list then it would show 8 in C3. Any ideas if or how I can do this? Thanks so much for your help....I'm always amazed by what people can do on Excel!!! Honey |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list with different values.
You can use a VLookup formula to return the value matching the name that
is selected. There's information in Excel's Help, and he http://www.contextures.com/xlFunctions02.html Honey wrote: Hello!!! Here is my problem... I have a drop down list and I want each item on it to display a differen value in another cell So if my list is: C2 Value Tom 10 Mary 8 sarah 4 Michael 2 So If I selected Tom from the drop down list it would display 10 in C3 or if I selected mary from the drop down list then it would show 8 in C3. Any ideas if or how I can do this? Thanks so much for your help....I'm always amazed by what people can do on Excel!!! Honey -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down list with different values.
There are several ways to do this, but which one is best depends on where you
are getting the list of names for the dropdown list in C2 from? I presume you used Data Validation to get them into it, but where do they come from? 1) Did you hand-type them when setting up the data validation, or 2) Do they come from a list of names somewhere else on the sheet? If the list is only 4-names long you could use a nested IF statement like: =IF(C2="Tom",10,IF(C2="Mary",8,IF(C2="sarah",4,2)) ) If you have typed the names in during while setting up Data Validation, the IF above would work, (but the IF is limited to 7 nest levels, or 8 names), you could use this formula in C3 =CHOOSE(MATCH(C2,{"Tom","Mary","sarah","Michael"}, 0),10,8,4,2) But if your list of names is coming from a list in cells elsewhere on the worksheet, then this will work, assumes the list of names are in H2:H5 =CHOOSE(MATCH(C2,H2:H5,0),10,8,4,2) Finally, if the list is on the sheet, and if you have an empty column next to it, you could turn it into a table by entering the value for each name into the column next to it, so it might look like this over at H2:I5 H I 2 Tom 10 3 Mary 8 4 sarah 4 5 Michael 2 and then you could use something like this in C3: =VLOOKUP(C2,H2:I5,2,0) "Honey" wrote: Hello!!! Here is my problem... I have a drop down list and I want each item on it to display a differen value in another cell So if my list is: C2 Value Tom 10 Mary 8 sarah 4 Michael 2 So If I selected Tom from the drop down list it would display 10 in C3 or if I selected mary from the drop down list then it would show 8 in C3. Any ideas if or how I can do this? Thanks so much for your help....I'm always amazed by what people can do on Excel!!! Honey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
auto updating list | Excel Worksheet Functions | |||
Drop down list from filtered data | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |