Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bobby Mir
 
Posts: n/a
Default 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   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

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   Report Post  
Bernard Liengme
 
Posts: n/a
Default

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   Report Post  
paul
 
Posts: n/a
Default

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   Report Post  
Bobby
 
Posts: n/a
Default

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   Report Post  
Bobby
 
Posts: n/a
Default

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   Report Post  
Bobby Mir
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing cell references automatically Bigweed New Users to Excel 3 April 28th 05 12:27 PM
Changing Cell formats to date fields automatically PCLIVE Excel Worksheet Functions 3 April 12th 05 10:34 PM
changing value of a cell based on another cell color Gary Excel Discussion (Misc queries) 2 January 30th 05 11:19 AM
Selecting an Item from a List and getting a different item to pop. Matt Excel Worksheet Functions 1 December 7th 04 03:37 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"