1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•