How do I Duplicate Cells fro 1 Worksheet in another dependant on Item Number.

[Pazz1970]

Could anyone advise how I could achieve the following please:-
I have a spreadsheet with 2 worksheet's - Purchases & Sales
Within the Purchases sheet I have several columns for things such as:-
Purchase Date, Item Number, Item Description, Item Type, Where Purchased, Purchase Cost.
Within Sales Re columns for:-
Sale Date, Sale Price
Within the Sales sheet I also have duplicate columns (as those in Purchases) for Item Number, Item Description, Where Purchased, Purchase Cost.
The Purchases sheet will all be completed by hand and each Item will have unique number. What I would like to do is be able to type the Item Number in the Sales sheet and for excel to automatically fill in the corresponding details for that item in the duplicate cells in the Sales sheet.
Hope I've explained my requirement well enough. It would be preferable to be able to do this with 'normal' Excel functionality rather than using VB. But, if there s no alternative this would be acceptable.
Thanks in advance.

[FDibbins]

Hi Pazz and welcome to the forum

If I understand you correctly, you can achieve what you are trying to do by using the vlookup finction. the syntax is...
=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-value,FALSE) FALSE is for finding an exact match

Without seeing a sample of what you are working with, I cant advise on what the ranges etc would look like, but if you still have a problem, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[bebo021999]

You can use VLOOKUP, INDEX and MATCH to do this. Anyway, is there any chance to post a dummy file?

[Pazz1970]

Here is an example of what I could have. 2 Worksheets, you will see that some columns in Sales replicate those in Purchases. When I type the Item Number into Sales worksheet I want the other cells associated with this item in the Purchases worksheet to be filled into the corresponding cells in the Sales worksheet automatically.

Thanks.

[FDibbins]

in column A, use this, copied down...
=INDEX(Purchases!$A$1:$F$20,MATCH(Sales!$B2,Purchases!$B$1:$B$20,0),1)
in all other columns, use this, copied down and across...
=INDEX(Purchases!$A$1:$F$20,MATCH(Sales!$B2,Purchases!$B$1:$B$20,0),MATCH(C$1,Purchases!$A$1:$F$1,0))

If you could change the heading on both sheets to just "date", then you could use the 2nd formula through-out