Lookup & then paste cells

[chris100]

Hi all,

For me this is by far the most complicated thing i've tried to pull off using VB in excel - but then i'm still not very good
Any help here very much appreciated:

Imagine a large number of products on an invoice. Simply put the Sheet (INVOICE) is as follows:

PRODUCT PRICE Q

APPLE £5.00 1
BANANA
CHERRY £3.50 3

Note that not all items on the invoice are necessarily sales - only those with PRICE and Q filled in. The items position on an invoice can also change daily with change of stock, seasons etc.

Now when a macro is run, I would like it to transfer to the another sheet (STOCK):

A B C D E F

PRODUCT PRODUCT PRODUCT
APPLES BANANA CHERRY
Q P Q P Q P

1 £5.00 15 £2.20 3 £3.50
2 £4.50 1 £2.90
4 £4.80

This wouldn't be too difficult for me if the item positions on INVOICE didn't change regularly. So what i'm looking for is something that will lookup Apple on the invoice, transfer P and Q to the appropriate place in the next sheet, then do the same for all the other products.

As an extension of this each invoice has an invoice number that i would need transferred over for each item....but i guess one step at a time?

Thanks for taking the time to cast your eyes over my problem and any advice. I have looked through the forum but find it difficult to adapt anything i've come across.

[chris100]

The columns didn't come through properly (i wasn't being lazy!)

The INVOICE is easy enough to figure out.
STOCK has two columns for each item with quantity and price.

[chris100]

Help please anyone - i'm desparate to find aa solution to this one.

Thanks