Automatic invoice generation

[senthilrajagopi]

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.

 

[oldbrewer]

		MODEL	CODE	QUANSTOCK	RECEIVED		ALL A'S	SUM OF A'S	IS SUM LESS OR MORE THAN REQUIRED TOTAL	IF MORE HOW MANY MORE				A	600		150
		A	124	200	15-Aug		200	200	LESS		200	15-Aug
		A	123	250	15-Sep		250	450	LESS		250	15-Sep		450
		A	124	600	15-Oct		600	1050	MORE	450	150	15-Oct		600
		A	124	300	15-Nov		300	1350	MORE	750	FALSE	15-Nov		150
		A	124	500	15-Dec		500	1850	MORE	1250	FALSE	15-Dec
		B	124	500	16-Aug
		B	123	300	16-Sep
		B	123	200	16-Oct
		B	123	400	16-Nov
		B	123	600	16-Dec
		THE TABLE HAS BEEN SORTED
		BY MODEL ASCENDING THEN
		RECEIVED ASCENDING
							ALL NUMBERS IN THE ABOVE TABLE
							ARE DERIVED FROM FORMULAS
		YOUR REQUIREMENT
							COLUMN M (200,250,150)
		600	A				SHOWS YOU WHAT TO TAKE
		SHOULD BE MADE UP OF			IN COLUMN P 450 IS THE LAST "LESS" TOTAL
							AND 600 IS THE FIRST MORE TOTAL
		200	A	15-Aug			AND 150 IS THUS REQUIRED TOTAL TO TAKE
		250	A	15-Sep
		150	A	15-Oct			THE 150 IS TAKEN TO CELL S1 FOR CONVENIENCE
							THE DATES IN COLUMN N ARE PULLED ACROSS FOR CONVENIENCE
							THE "YOUR REQIREMENT" SUMMARY TABLE
							IS 5 ROWS DEEP BUT SHOWS BLANKS
							AS THE ORDER IS COMPLETED IN THREE ROWS

<colgroup><col span="2"><col span="2"><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>