Need to check for an item number and retrieve certain information

Hello,
I have been given a task and currently, i am struggling. It is basically a price list that turns into a quote however some checks are needed to be done before hand. For example, there's item numbers and if an item number appears, it needs to be able to grab key information from that row containing the item number (sale price, and cost of product.) Once grabbing this information i need it to work out the gross margin.

The idea behind it is that the information on sheet one is being extracted from Sage and will be changing in turn with with order. The second sheet will be a sheet full of the inventory, cost, sale price, and the stock (this sheet wouldnt be edited but more used as a 'master' sheet). The third sheet will be a quote with the total price and the cost price.

The first sheet could vary in the results depending on the order, because of this i need a way to copy over the key data to the thrid sheet, no matter how many things are ordered, but for it to still look presentable. (By this i mean if theres two items ordered, i don't want there to be a massive gap between the list of the two items and total, but if theres twenty items being ordered i also dont want to overlap the other parts of the sheet.)

Any help whatsoever would be appreciated.
Thanks

Example.xlsx

I hope this is enough, once again the first sheet is basically an extract from sage of the order, the second sheet is a master document, the third sheet is basically the quote with all the figures sorted out. the first sheet can have any number of items ordered and will need to be reflected on the third sheet for the quote (what i mean by this is that if theres 50 items ordered individually, the subtotal and other formulaes would need to move down also, if thats even possible.)

The data on the third sheet needs to be extracted based on the item numbers (gained from the order from the first sheet) for the sale prices, and the cost of product, as well as the quantity from the first sheet.

As before mentioned, im really unsure on how to do these, hell, it might not even be possible for all i know. But i really do appreciate any help or advice you can give.

Thanks

Originally Posted by Olly

Something like:

Please Login or Register  to view this content.

Thank you very much for this, ive made a few adjustments and its working very nicely!

I really appreciate your help. Thank you!

I had to make a few changes to the document and im having trouble getting the 'Cost of product' to appear.

The things that have changed so far are as follows:
Cost of products is now in ws2 starting from cell C3 downwards and many more items have been added to the 'master sheet'.

Would any body be able to help me with this?

Thanks again Olly, with a little more fiddling i managed to find a solution, thanks again