Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2001
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - Worksheets

    I am working on a project which involves a number of worksheets in a workbook. 1 of the sheets(called STOCK)contain (horizontally across the page) Serial number, Stockcode-description, and unit price. The other sheet (call INVOICES) contain Quantity, Stockcode-description, unit price and total cost. I wish to type the stockcode in the Invoice cell and the description and unit price would automatically show in the adjacent cells respectively. Since I have not used Excel for some considerable time a cannot fathom out how to achieve this result.

    Any suggestions?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Worksheets

    Would it be possible to separate the Stockcode and description into two cells? If so, then you could use the Vlookup formula Function. Let me know if you think this may work for you.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Worksheets

    Hi:
    Basically, you would use an eq similar to:

    =VLOOKUP(B3,Ref_area,Col_Index_#,False)

    where your stockcode# would be in cell B3,
    the ref-area would encompass all values for "Quantity, Stockcode-description, unit price and total cost",
    Col_Index_# would be 3 for retrieving the "Unit-Price. Now, to be more precise, we need to have answers to the following questions:
    1) What do you mean by "the Invoice cell"? is this a cell in another sheet or in the Stock Sheet?
    2) By "Stockcode-description", do you mean that the stockcode and the description are combined together with a hypon? If so and if you "wish to type the stockcode" and retrieve "the description and unit price" in different cells, then, in place of "B3" you would need to use:
    LEFT(B3,FIND("-",B3)-1).
    3) This brings up the third question: if you have "Stockcode-description" in both tables, then why not enter the "Stockcode-description" in cell B3 and use
    =VLOOKUP(B3,Ref_area,2,False)
    where Ref_area,in theis case, would only encompass the last three columns of your "INVOICES" sheet.
    Best I can do with the info you provided.

    Regards
    Stephen

    LEFT(B3,FIND("-",B3)-1)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Worksheets

    Bewens is right I think, this problem will be easier if you separate the Stockcode and the Description. With luck your stockcodes will all have the same number of characters. If so you should be able to select this column then go to Data/Text to Columns Wizard, use the Fixed Width option and drag the line to the right end of your Stockcode column, then delete any other lines XL wants to put in your Description column.

  5. #5
    Lounger
    Join Date
    Feb 2001
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Worksheets

    Thanks for the information. I have tried a demo on this approach and yes it appears it will work. A re-hash of the current worksheets will be necessary.

    Regards

  6. #6
    Lounger
    Join Date
    Feb 2001
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Worksheets

    Thanks...

    I am now looking into this approach. Strange as it may seem, since I put Excel away for a while now coming back to it seems unfamiliar, especially now moving from Excel 97 to 2000. However, thanks again for all suggestions.

    regards

Posting Permissions

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