auto generate running balance from 12 sheets into a summary sheet

[jaydon123]

I would like to create and auto generate on the running balance sheet. When i input to data on the 12 months if would be auto generate onto the running balance sheet.

For example,
when the data I pick from "Jan" the the month on the running balance sheet would be 1,

The date on the running balance sheet would come from "Date Received" on the income tracker table or "Date" on the Expense tracker table from Jan-Dec sheets.

Description on the running balance sheet would be "income" if it come from the income tracker table or "Explanation of Expenditure" on the Expense tracker table from Jan-Dec sheets.

Category on the running balance sheet would be from "Category" on the Expense tracker table from Jan-Dec sheets.

Income Source on the running balance sheet would be from "Source" on the income tracker table from Jan-Dec sheets.


Credits on the running balance sheet would be from "amount" on the income tracker table from Jan-Dec sheets.

Debits on the running balance sheet would be from "amount" on the Expense tracker table from Jan-Dec sheets.

Balance on the running balance sheet would be a formula of cell H2 would be " =[Credits]-[Debits] " and the entire H column would the formula of the last balance + [Credits]-[Debits]



Thank you in advance

[Richard Buttrey]

Hello and welcome to the forum.

Please take this in the constructive way it is intended.

You are making the same mistake that we see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Your workbook is a case in point. As it stands summarising and reporting the running balance data, and indeed other stuff would be extremely complicated and I doubt there are any straightforward formulae. Macros could do it but that would be using the proverbial sledgehammer. Get the data in the right format in the first place and reporting problems disappear.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that will contain all the data for the whole year. Use the following columns.

Date
Amount - different sign for Income or Expenditure, use a conditional format to trap data entry conflict with Income or Expenditure field
Income/Expenditure
Category
Method
Description
Beneficiary
Tax Deductible Item?
Source
Date Received
Tax?


The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

Incidentally try to avoid merged cells like E:H in the current Expenditure section. Most of us avoid them like the plague. They just cause too many problems for reasons with which we needn't concern ourselves at the moment.