Selected Answer
I messed up your beautiful workbook. This is what else I did.
- I created lots of named ranges. Please look at the Name Manager (Formulas tab) to get the hang of them.
- I added a worksheet "Params" (which is hidden). It has two overlapping named ranges. One lists transactions by name, the other associates them with either debit or credit. This arrangement replaces the DR/CR column I recommended last time.
- The list in named range "Transactions" feeds the dropdown in column D on the Ledger sheet.
- In the same way the "Customers' named range feeds the validation in Ledger!Column A, and the "Loans" named range feeds the dropdown in Ledger!Column E.
- The "Customers" worksheet has overlapping named ranges "Customers" and "Interest". You can set the interest separately for each customer, for normal and overdue accounts. But you have to set up a customer before you can make an entry in the ledger for him.
- The "Loans" sheet gives a number of each loan. Observe to enter only the number (1, 2 or 3 etc) and not "ABC-001". Set the number format to match your numbering requirement. Note that the Ledger sheet needs an integer although it displays the way you format the cell.
- For each loan you set a customer, the day it was granted and teh duration. The due date is calculated by a formula.
- On the Ledger sheet there is a formula in column F which has no output until you select the transaction type of "interest" and have specified a loan. It will then calculate the interest for one month, for the balance of the indicated loan, using the customer's normal or overdue rate depending upon the entry date and the loan maturity. If the entry isn't for interest, just over-write the formula with whatever.
So, the work flow is to create a formatted, blank row in the Ledger worksheet, select a customer from the dropdown in column A (set up the customer first if new), enter the date, select the transaction type from the dropdown (set up all transactions on the Params sheet beforehand), Select a loan number from the dropdown if the transaction concerns a loan (else leave blank. Loans must be set up before you can refer to them), enter the transaction amount. If the entry refers to a loan the balance of that lown, after the new entry, will be shown in column H.
You will not see the column G which is hidden. It converts the absolute amount of the transaction into a positive or negative number depending upon the setting for the entry type on the Parms sheet.
The Customers sheet will show each customer's balance. The formula shows the total balance (Savings + Loans).
The Loans sheet will show the balance of each loan. If it is overdue it will be shown red and bold (conditional formatting).
It isn't exactly what you wanted, I know. But after you pretty things up a little it will do the job for which it was designed, I hope :-)