I have a data set and I need to apply payments to balances,
Attached is a sample
Sheet 1 is the transactions
Sheet 2 is opening payments
Sheet 3 is expected results
Thanks in advance
I have a data set and I need to apply payments to balances,
Attached is a sample
Sheet 1 is the transactions
Sheet 2 is opening payments
Sheet 3 is expected results
Thanks in advance
There is no need to use VBA to accomplish this tasks. Excel tables and formulas will do.
Excel tables have a lot of benefits. One of them is that they know how big they are so when you write formulas using them, they always reference the right amount of rows even when you add or delete rows. When you add data to the row immediately below the table, that row becomes part of the table. Furthermore, all the formulas, validations and formats get copied down to that row.
So I took some liberties with the transactions table. I moved the date to the first position, since it is easy to enter the date manually. This copies down the validations for the rest of the columns. There is a Tables Tab where you can add Trans Codes and Data SRC. These tables provide data validation for columns C and E in the transactions table. You can either type these values in or use the drop-down list in the cell. Column B is validated from column A in the Balance Table
I also formatted the amount to currency.
The balance sheet shows the current worth of the accounts. Column B contains the Opening Balance - I put some test values in there. Column C has the formula to summarize the transactions.
=[@[Opening Balance]]+SUMIFS(Table_Transactions[AMT],Table_Transactions[ACCT_ID],[@[ACCT_ID]])
Now you see another advantage of tables, formulas are expressed in terms of table names and column headers. This makes them easier to understand.
If you need a new account, just add it to the Balance Table.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks