Home |
Search |
Today's Posts |
#1
|
|||
|
|||
changing value of a cell by selecting an item from a drop down list
hi there,
i'm looking to find out if anyone can help with a certain task i've been trying to accomplish. what i have in an excel sheet is a list of reference codes to different items set up as a drop down list. what i'm trying to do now is, whenever i select an item in the drop down list, i want a different cell on the sheet to change in value, number value that is. does anyone have any suggestions on how i could go about this? the drop down list is pretty long...i'd say atleast 50 or so items. example: i choose reference code "2B5-2312" from the drop down list, and then i want cell B1 to change to whatever the value of that code is...so in this case its a numeric value of 12. again, choosing reference code "TXL-3451" should give a numeric value of 24 in cell B1. hope this helps explain what i'm trying to do! any help would be appreciated. thanks, bobby |
#2
|
|||
|
|||
example:
i choose reference code "2B5-2312" from the drop down list, and then i want cell B1 to change to whatever the value of that code is...so in this case its a numeric value of 12. again, choosing reference code "TXL-3451" should give a numeric value of 24 in cell B1. And how is anyone supposed to figure this out? Is there an algorithm that determines what value is assigned to what reference code? -- Vasant |
#3
|
|||
|
|||
Have a look in Help under VLOOKUP
Come back with questions if you need help after reviewing the way this function performs. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bobby Mir" wrote in message ... hi there, i'm looking to find out if anyone can help with a certain task i've been trying to accomplish. what i have in an excel sheet is a list of reference codes to different items set up as a drop down list. what i'm trying to do now is, whenever i select an item in the drop down list, i want a different cell on the sheet to change in value, number value that is. does anyone have any suggestions on how i could go about this? the drop down list is pretty long...i'd say atleast 50 or so items. example: i choose reference code "2B5-2312" from the drop down list, and then i want cell B1 to change to whatever the value of that code is...so in this case its a numeric value of 12. again, choosing reference code "TXL-3451" should give a numeric value of 24 in cell B1. hope this helps explain what i'm trying to do! any help would be appreciated. thanks, bobby |
#4
|
|||
|
|||
If you are using the drop down box from the forms toolbar you can use your
list as the range for =index(data_range,row number(cell link for drop down box),Column number in range for the number say 25 related to your lookup value say 2t5-234). paul remove nospam for email addy! "Vasant Nanavati" wrote: example: i choose reference code "2B5-2312" from the drop down list, and then i want cell B1 to change to whatever the value of that code is...so in this case its a numeric value of 12. again, choosing reference code "TXL-3451" should give a numeric value of 24 in cell B1. And how is anyone supposed to figure this out? Is there an algorithm that determines what value is assigned to what reference code? -- Vasant |
#5
|
|||
|
|||
no there isn't an algorithm...there are 2 columns in the spreadsheet, one
has the reference codes, which is just considered text, and the column next to it has the corresponding value to the code. kind of like this: Column A Column B Refcode1 25 Refcode2 12 .... ... so when i choose refcode1 from the drop down list (which is located elsewhere on the spreadsheet), i'm trying to get a cell filled in with the corresponding value (the cell is not located in the same column or row as the drop down list). thanks, bobby "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... example: i choose reference code "2B5-2312" from the drop down list, and then i want cell B1 to change to whatever the value of that code is...so in this case its a numeric value of 12. again, choosing reference code "TXL-3451" should give a numeric value of 24 in cell B1. And how is anyone supposed to figure this out? Is there an algorithm that determines what value is assigned to what reference code? -- Vasant |
#6
|
|||
|
|||
i've figured out a rather crude way of doing things. i've got my list
referencing a cell that's hidden on the spreadsheet. basically there's 46 items in the drop down list and this cell just indicates a number from 1 to 46 depending on what item is chosen on the list. then i have written a formula to read that cell and then change all other relevant cells with their corresponding values. problem is i can't list more than 7 IF statements as we all know, and the more practical way to do it would be using VLOOKUP. i can't seem to figure out how to go about it. i know i can define formulas and create sets of nested IF statements eventually leading to a 46 IF statement formula. here's an example: item1 selected in drop down list cell N11 then indicates a value of 1 then we'll say cell D32 has this formula in it =IF(N11=1, 'Sheet2'!D4, 0) so D32 changes to the value of D4 on Sheet2. this may sound rather mundane and a long route to what i'm looking for, so if you guys have any suggestions i would definitely appreciate it. thanks!! bobby "Bobby Mir" wrote in message ... hi there, i'm looking to find out if anyone can help with a certain task i've been trying to accomplish. what i have in an excel sheet is a list of reference codes to different items set up as a drop down list. what i'm trying to do now is, whenever i select an item in the drop down list, i want a different cell on the sheet to change in value, number value that is. does anyone have any suggestions on how i could go about this? the drop down list is pretty long...i'd say atleast 50 or so items. example: i choose reference code "2B5-2312" from the drop down list, and then i want cell B1 to change to whatever the value of that code is...so in this case its a numeric value of 12. again, choosing reference code "TXL-3451" should give a numeric value of 24 in cell B1. hope this helps explain what i'm trying to do! any help would be appreciated. thanks, bobby |
#7
|
|||
|
|||
no new suggestions on this guys?? any help would be appreciated.
thanks in advance, bobby "Bobby" wrote in message ... i've figured out a rather crude way of doing things. i've got my list referencing a cell that's hidden on the spreadsheet. basically there's 46 items in the drop down list and this cell just indicates a number from 1 to 46 depending on what item is chosen on the list. then i have written a formula to read that cell and then change all other relevant cells with their corresponding values. problem is i can't list more than 7 IF statements as we all know, and the more practical way to do it would be using VLOOKUP. i can't seem to figure out how to go about it. i know i can define formulas and create sets of nested IF statements eventually leading to a 46 IF statement formula. here's an example: item1 selected in drop down list cell N11 then indicates a value of 1 then we'll say cell D32 has this formula in it =IF(N11=1, 'Sheet2'!D4, 0) so D32 changes to the value of D4 on Sheet2. this may sound rather mundane and a long route to what i'm looking for, so if you guys have any suggestions i would definitely appreciate it. thanks!! bobby "Bobby Mir" wrote in message ... hi there, i'm looking to find out if anyone can help with a certain task i've been trying to accomplish. what i have in an excel sheet is a list of reference codes to different items set up as a drop down list. what i'm trying to do now is, whenever i select an item in the drop down list, i want a different cell on the sheet to change in value, number value that is. does anyone have any suggestions on how i could go about this? the drop down list is pretty long...i'd say atleast 50 or so items. example: i choose reference code "2B5-2312" from the drop down list, and then i want cell B1 to change to whatever the value of that code is...so in this case its a numeric value of 12. again, choosing reference code "TXL-3451" should give a numeric value of 24 in cell B1. hope this helps explain what i'm trying to do! any help would be appreciated. thanks, bobby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing cell references automatically | New Users to Excel | |||
Changing Cell formats to date fields automatically | Excel Worksheet Functions | |||
changing value of a cell based on another cell color | Excel Discussion (Misc queries) | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |