Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inventory system (2002)

    I am not a programmer, but I have been tasked with creating an in-house inventory system using Excel. I have created a drop down list in one cell, based on the value chosen, I want to populate an ajacent cell with another value. in other words, IF A is chosen from the list populate B2 with A0.
    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Your question is a bit vague. If I take it literally, and A is in cell A1 and you want A0 in cell B2, the you could use the formula:

    =A1&"0"

    That will add a zero to the end of whatever is chosen from the list.

    I have a feeling this is not what you really want, but I can't tell from your description. Can you give us a better description of what is the relationship between A and A0?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    May 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Yes. In A1 there is a drop down list populated with products we keep in our office, for example, Opti-flow pens. When this selection is made, I need to populate B2 with preassigned product code: z-pen002.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inventory system (2002)

    Can you tell us how the dropdown list was created:
    1. <LI>using Data | Validation
      <LI>from the Forms toolbar
      <LI>from the Control Toolbox
    The formula in B2 to look up the product code will depend on this.

  5. #5
    New Lounger
    Join Date
    May 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Using Data Validation

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Newguy,
    Take a look at the attached workbook.
    Does this do what you want?

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inventory system (2002)

    I assume you have a table of product (from the list) and the product code.

    If the product list is in d1:e10, and e1:e10 has the codes

    If A1 has the cell with validation. the formula:
    <pre>=VLOOKUP(A1,D1:E10,2,0)</pre>


    will give you the code of the product from validation list.

    Change ranges as appropriate.

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inventory system (2002)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I think in B3 you meant:
    =IF(A3="","",VLOOKUP(A3,$A$20:$B$26,2,FALSE))

    Instead of:
    =IF(A3="","",VLOOKUP(A3,A20:B26,2,FALSE))

    otherwise, when you copy it the lookup table is relative...

    Steve

  9. #9
    New Lounger
    Join Date
    May 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Yes Chuck.
    Thanks!

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Steve,
    Yup! I was in a hurry as I thought I might actually have a chance of being the first to reply....
    My bad.
    Thanks for the catch.
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Attached is a corrected workbook.
    Steve caught a blunder in my formulas.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  12. #12
    New Lounger
    Join Date
    May 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory system (2002)

    Thanks again Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •