IF Formula (Or maybe something else...)

[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.

I know a bit about Excel, however I've been messing around with this for about a week now and can't find a solution.

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

Please find the workbook link at the top.

P.S Ignore the Formula on the Consignment stock sheet that comes out at 0, this was me frustrated and messing around!

Thanks!

 

[AlphaFrog]

On Sheet "Stock Out", if you could have the PO Number in a row by itself without the Date, that would make it a lot easier. Maybe put the PO w/Date in row 2 if you want.

Assuming Stock Out row 3 has just the PO Numbers (without date), put this in cell C4 on sheet Stock In Consignment...

=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)))

And copy it down and across.

Index-Match-Match

 

[Porterrob]

Thanks for your help.

So I did everything you said, however on the consignment stock sheet no values are showing.
I was trying to sort it out and managed to get it all to say #N/A and from what the link you posted said was to convert the cell to text but just before I could do this I had a power cut and now I can't remember what I did to get to that point.

Any suggestions?