senthilrajagopi
New Member
Hi Excel Guru's,
Model PO No Line Item Qty Del Date
A 124 1 200 15-08-2015
B 123 1 300 16-09-2015
A 123 2 250 15-09-2015
B 124 2 500 16-08-2015
A 124 3 600 15-10-2015
B 123 3 200 16-10-2015
A 124 4 300 15-11-2015
B 123 4 400 16-11-2015
A 124 5 500 15-12-2015
B 123 5 600 16-12-2015
If i invoice enter Model & Qty, then it should first consider oldest delivery date, then take PO no,
Line item and qty. After invoice qty should be detected from PO and balance to be shown as pending PO..
For Example, if i dispatch Model "A" - 600 Nos, it should take
Model Del Date PO No Line Item Total Balance
A 15-08-2015 124 1 200 0
A 15-09-2015 123 2 250 0
A 15-10-2015 124 3 150 450
Give me the Formula to come out of this issue.
Thanks & Regards,
Senthilrajagopi.
Model PO No Line Item Qty Del Date
A 124 1 200 15-08-2015
B 123 1 300 16-09-2015
A 123 2 250 15-09-2015
B 124 2 500 16-08-2015
A 124 3 600 15-10-2015
B 123 3 200 16-10-2015
A 124 4 300 15-11-2015
B 123 4 400 16-11-2015
A 124 5 500 15-12-2015
B 123 5 600 16-12-2015
If i invoice enter Model & Qty, then it should first consider oldest delivery date, then take PO no,
Line item and qty. After invoice qty should be detected from PO and balance to be shown as pending PO..
For Example, if i dispatch Model "A" - 600 Nos, it should take
Model Del Date PO No Line Item Total Balance
A 15-08-2015 124 1 200 0
A 15-09-2015 123 2 250 0
A 15-10-2015 124 3 150 450
Give me the Formula to come out of this issue.
Thanks & Regards,
Senthilrajagopi.