sengleong98
New Member
- Joined
- Mar 24, 2009
- Messages
- 1
Hi,
I am new to the forum and are desperately in need of help.
Basically, I created 2 sheets:-
1) Purchase Order Tracking (PO)
2) Material Inventory (MTX)
Sheet (PO) is used to store each PO issued in every row. I will manually key in the vendor commit date and qty..
I use sheet (MTX) to calculate the usage of 300 types of parts (300 part numbers(P/N)). If a P/N has insufficient qty., I can use sumproduct to lookup the total outstanding PO qty. in sheet (PO). The formula in sheet (MTX) looks like this:-
=SUMPRODUCT(('PO'!$C$3:$C$1202=MTX!$C4)*($C4<>"")*(MTX!$AQ4<=0)*('PO'!$AC$3:$AC$1202="")*('PO'!$N$3:$N$1202))
- Column C in both sheets refers to P/N.
- Column AC in sheet (PO) refers to the actual received date.
- Column N in sheet (PO) refers to the PO qty.
- Column AQ in sheet (MTX) refers to the balance qty.
So, the above formula is just:-
sumproduct((sheet(PO)P/N matches sheet(MTX)P/N) and (sheet(MTX)P/N is not blank) and (balance qty. is <=0) and (sheet(PO)received date = blank (meaning part haven't received), sum(sheet(PO)PO qty.))
This is the easy part...
Now, I need to use formula(s) to find out the 1st commit date in 1 column and then 2nd commit date in another column... that is more than I can handle...
I used =IF($AS4>0,VLOOKUP(C4,'Mthly PO'!$C:$AA,25,0),"") but this is not correct because it will always return the 1st match (even if the PO had already closed). BTW, sheet (MTX)AS is the column I use to calculate the total PO qty..
I am still searching through this forum for answer, but would think asking the question might get faster result.
Please help...
I am new to the forum and are desperately in need of help.
Basically, I created 2 sheets:-
1) Purchase Order Tracking (PO)
2) Material Inventory (MTX)
Sheet (PO) is used to store each PO issued in every row. I will manually key in the vendor commit date and qty..
I use sheet (MTX) to calculate the usage of 300 types of parts (300 part numbers(P/N)). If a P/N has insufficient qty., I can use sumproduct to lookup the total outstanding PO qty. in sheet (PO). The formula in sheet (MTX) looks like this:-
=SUMPRODUCT(('PO'!$C$3:$C$1202=MTX!$C4)*($C4<>"")*(MTX!$AQ4<=0)*('PO'!$AC$3:$AC$1202="")*('PO'!$N$3:$N$1202))
- Column C in both sheets refers to P/N.
- Column AC in sheet (PO) refers to the actual received date.
- Column N in sheet (PO) refers to the PO qty.
- Column AQ in sheet (MTX) refers to the balance qty.
So, the above formula is just:-
sumproduct((sheet(PO)P/N matches sheet(MTX)P/N) and (sheet(MTX)P/N is not blank) and (balance qty. is <=0) and (sheet(PO)received date = blank (meaning part haven't received), sum(sheet(PO)PO qty.))
This is the easy part...
Now, I need to use formula(s) to find out the 1st commit date in 1 column and then 2nd commit date in another column... that is more than I can handle...
I used =IF($AS4>0,VLOOKUP(C4,'Mthly PO'!$C:$AA,25,0),"") but this is not correct because it will always return the 1st match (even if the PO had already closed). BTW, sheet (MTX)AS is the column I use to calculate the total PO qty..
I am still searching through this forum for answer, but would think asking the question might get faster result.
Please help...
