changing value of a cell by selecting an item from a drop down list

B

Bobby Mir

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
 
V

Vasant Nanavati

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.
Click to expand...

And how is anyone supposed to figure this out? Is there an algorithm that
determines what value is assigned to what reference code?
 
B

Bernard Liengme

Have a look in Help under VLOOKUP
Come back with questions if you need help after reviewing the way this
function performs.
best wishes
 
P

paul

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!
 
B

Bobby

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
 
B

Bobby

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
 
B

Bobby Mir

no new suggestions on this guys?? any help would be appreciated.

thanks in advance,
bobby
 
Top