Stock control problem - multiple vlookups

Hi All

I have an unusual problem.
I have a list of batches of products with an attached expiry date.
I have to analyse whether the stock will be consumed by my ongoing forecast before it can no longer be sold?????

On the attached file I have 3 sheets

BW forecast - gives the monthly ongoing forecast starting with the current month , by product.

330a - shows the quantity , expiring date , product , status and batch no.

Output sheet - this is where you genoiuses come in.

For each product I would like to list in rows every batch (including the batch number , the expiry date , the quantity and the stock status .

I believe this can be done with arrays but haven't got the slightest clue of even how to look up how to do this.

Once I have the above date for each product , I then need to ascertain how much of each batch will not be consumed by the ongoing forecast , so I can act on what will be left.

I know this is not an easy request but this is causing me 3 - 4 days solid work every month - and I'm sure there must be a way to do it - its just out of my ability.

Thanks in advance for any help.

Darren W

Boy,

Questions
You forecast seems linear. Is that the case?
Will "DAMAGED" items be sold?
In the quantity column the cell formats are different. F6 & F10 are not the same?
Why is it that some items do not have a storage location?

So please simplify your excel sheet. Only relevant info (row,columns to be used) in there.

Gr,
Ricardo

Hi


Thanks for the reply
Here are the answers to your questions

You forecast seems linear. Is that the case? Yes
Will "DAMAGED" items be sold? "DAMAGED" is a location in the warehouse - so this is not reliable to use. All stock with a status of "S" will not be sold , so needs to show as an exception. This is found in column E of sheet 330a.
In the quantity column the cell formats are different. F6 & F10 are not the same?
Why is it that some items do not have a storage location?This info comes direct from SAP (our ERP) not all locations are named. These can be left blank.

So please simplify your excel sheet. Only relevant info (row,columns to be used) in there.
I can't do this as the sheet is a download from SAP