How to use FIFO method in payments received from customers?

Hi Croweater. Please understand it this way.

1- No. For the same accounting year, different customers cannot have the same invoice number. I am working for a small company for which I manage the data. Our accounting system / software automatically generates the numbers from 1 to n. For each transaction the invoice number is auto-incremented. Once the accounting year is completed it is reset to 1 again. I hope this point is clear to you.

2- Payments are adjusted against respective customers who pays. We know which customer is paying. And it is adjusted against whichever invoice is older corresponding to that customer. Let us look at the below example.

Kelly has:
Invoice 1 dated 1-Jan-2020 with the total invoice value of $1,500 (Line item 1)
Invoice 1 dated 1-Jan-2020 with the total invoice value of $2,500 (Line item 2)
Invoice 2 dated 15-May-2020 with the total invoice value of $5,000 (Only one line item for this invoice)

She made payment of $2,000. It will be adjusted against the oldest invoice which is due. In this case Invoice 1's 1st line item will be updated as fully paid. Remaining $500 will be adjusted in 2nd line item and will be marked partially paid. The due will be $2,000 for that particular invoice (i.e Invoice 1).

3- No I am not looking for VBA. I am looking for some simple formula that can be incorporated.

4- This is explained in point 2.

Please help me with the solution. Attached the workbook with this message.

Thanks!!