Problem with Formula

[Porterrob]

http://www.box.net/shared/0ebhd83tg6

Hi Guys

So heres what I want to do.

I need to link up the PO's and part values on the Stock out and relevant values to the corresponding PO's on the Consignment stock sheet. (It makes sense when you look at the workbook)

For example

Column B on the Stock Out PO 90450 1st October
Value 10 against AH2-R010-0.1-K2. Now I need that 10 value to be matched to the corresponding PO number on the Consignment stock sheet and part.

What you have to understand is that I need a formula on the consignment stock sheet that will cover the stock out sheet as parts are added to it, so every time I add a PO to stock out I wont need to adjust or add a formula to link it.

Also what I would like to do is have it so that when the same part comes out on different dates but from the same PO, for the value in the consignment stock sheet to have the total.

The formula below seems to work on some PO but not others and I can't work out why. Also it doesn't seem to work on Excel 2003 and I need a version to run on that version of excel.

=IF(OR(ISERROR(MATCH($A4,'Stock Out'!$A:$A,0)), ISERROR(MATCH(RIGHT(C$3,5),'Stock Out'!$3:$3,0))),"",
INDEX('Stock Out'!$1:$1000,
MATCH($A4,'Stock Out'!$A:$A,0), MATCH(RIGHT(C$3,5),'Stock Out'!$3:$3,0)))

If anyone can solve this for me I will be in there debt!

Please find the workbook link at the top.

Thanks!