Automatic invoice generation

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.

Attached is a formula solution that assumes that the delivery dates will already be in order as in the example. If they are not, then it would be an easy fix to sort them, assuming that they are in one of the Excel date formats. The key formula that drives this solution is:

Please Login or Register  to view this content.

Delivery Schedule.xlsx

Let me know if you have any questions.

Array formula J3 (for column J to M),then dragged across

Please Login or Register  to view this content.

Array formula N3 ,then dragged down

Please Login or Register  to view this content.

Array formula O3 ,then dragged down

Please Login or Register  to view this content.

Hi kvsrinivasamurthy,

Thanks for your response. and it helped me to great extent.

My requirement is little bit expanded and i attached the excel book in requirement sheet.

(My requirement is To bring price column and multiple models in consequent lines)

Give me the appropriate valuable solution to come out of this issue.

Thanks & Regards,

Senthilraja.V

Formula becomes very complex. I have gven macro .
Code

Please Login or Register  to view this content.

Thanks For quick response.

But if you given me this function through formula it is helpful for me

Regards,

senthilraja

Hi excel Gurus

Kindly give me the solution use with formulaes

Regards,

Senthilraja

Pl see file.It took more time to get formulas.

Formula for K3

Please Login or Register  to view this content.

Formula for L3

Please Login or Register  to view this content.

Formula for O3

Please Login or Register  to view this content.

Formula for P3

Please Login or Register  to view this content.

Thanks a lot

Glad that you got an answer that works for you. Please take a moment to mark the thread 'Solved' using the thread tools above your first post. Hope that you have a good day.

Hi kvsrinivasamoorthy,

that formulae works great, but i want to one clarification. If zero in po qty than is it possible to omit the entire line and given the value in Invoice line item

Give me the reply if it is possible

Regtards,

Senthilrajagopi