Forum Discussion
Accounting Dilemma - Apply Amounts Received to Open Invoices
Hello,
I have an issue with applying an amount received to a list of open invoices.
Lets say I receive $1000 and there are 9 open invoices all with different amounts.
I am looking for a simple way to display a possible list that would sum to 1000.
I am wondering if anyone has developed a solution for this.
There was a "challenge" competition on Mr Excel and a solution was provided but I didnt see an excel file attached but there is code posted but no instructions how to use the code (I am not a programmer) and setup of the excel file. Maybe there is some new functionality in excel that deals with this issue or someone else has a better solution. Or possibly Power Query has this functionality.
Mr Excel:
Accounts Receivable Challenge - MrExcel Challenges - MrExcel Publishing
Grateful for your assistance.
I have provided a sample file
3 Replies
That is a prevalent problem with VBA solutions because most people who work in accounting do not have coding skills for VBA. Let statement provides for 128 variables but does not have recursion so the user is left with VBA user defined function.... Lambda has not been rolled out yet so Lambda would be viable after the rollout... meanwhile you would do something like:
you would stop applying when the unappliled reaches "0" (zero), then do a power query and add a column using table.selectrows against the worksheet where unapplied not < 0 and >0
cheers
Tony2021 As an accountant myself, I would assume you have some sort of accounting system. If a customer does not supply the invoice numbers paid I would ask for a clarification or just apply the payment to the oldest invoices and leave whatever is left as a partial payment to the next oldest invoice. Most accounting systems will allow you to do so. I, thus, wonder why you want to try to do this is in Excel. It's quite meaningless! Suppose you have three open invoices, each $500 and you receive $1000 without any further information. Which two of these would you want Excel to pick as paid?
This can be done with solver. The attached file shows the formulas i entered in the worksheet and the restrictions of the solver.
