Dear Friends,
For a longgggggg time, I am thinking about publishing this post. This is useful for home makers.
This excel book allows her to record the daily expenses in a sheet and summary of the expenses will be updated in a separate sheet automatically.
OK now I will start the technical details. There are two sheets in this workbook
- Transactions (to enter the daily expenses)
- Summary (to generate summary report)
The Transactions sheet has the following columns
No | Date | Account | Amount (SGD) | Narration |
And the summary sheet the following columns
Account | Amount |
Now I will explain how the solution is implemented. I have created the following range names
- ListOfAccounts
- TxnAccount
- TxnAmount
In the Transactions sheet, I will enter the transactions as it occurs.
Column | Description |
Account | I used Data Validatation for a Combo box selection. |
No | used a formula to generate number series (=A2+1). While entering the daily expenses I will not type the number first. After completing entry of all expenses, I will double click on the last number. |
Amount | User Data Entry |
Narration | User Data Entry |
In the Summary sheet, the summary will be updated automatically
Column | Description |
Account | Usually it is Permanent List. New account heads are added here only. Since the ListOfAccounts rangename points this list, any new addition here will be reflected automatically in the Transaction sheet, Account Column |
Amount | Total Amount for the account head will be updated here. I used SUMIF formula for this purpose. =SUMIF(TxnAccount,A2,TxnAmount) |
I know that this pretty simple yet powerful (since used by powerful homemakers!!!)
I Assure you my support. Never hesitate to drop a mail to me at karthiganesh@yahoo.com
