Automatically summarizes entries from multiple sheets

Originally Posted by Jeleine

Hi Excel Guru,

I already posted a similar question but I think the question is kinda broad. I will try to explain it clearly.

Every month transactions are recorded in each sheet, specifically the daily income and expenses. I have 12 sheets Jan - Dec. My main goal is to have a summary of all loans borrowed from January to December and it will be recorded on the 13 sheet name Loans. To give me a enough view how much are my receivables.

Hello and welcome to the forum.
Can I question why you capture data in in individual monthly sheets?

You are making the same mistake that I 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 typical example.

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 contains the columns you currently have in a typical month and list records underneath each other as they occur

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.

Originally Posted by Jeleine

Hi Mr. Richard,

Thank you for the quick reply.

The reason I made a monthly transactions to know the monthly income and monthly expenses at the end of the month. But time and time, I need to know the total Loans incurred by each person to give an idea of how much the receivables. I know sorting out is quiet easy but I need an overview for the whole, Jan - Dec in one sheet. I could also do this by consolidating all the sheets, but it would give me the details of the loans. And I wanted it automatically. How do it? do I need macro program or simple formulas only?

Hope you get what I mean.

Indeed I do but with respect you are misunderstanding what Excel can do for you given the right database layour. You can get all the stuff you mention and any combination / permutation of the time/expense/people relationships and totals in seconds. But you won't get this unless you're prepared to keep the data in the layout I suggest - which isn't a million miles from what you're doing now. Believe me unless you do it this way you are just complicating the whole business.

If you're happy to change let me know and I'll show you how.

Hi,

This is the sort of thing I had in mind.

The Pivot Table is extremely flexible and you can change the analysis just by dragging and dropping the fields around in the PT field List Window. If you have Excel 2010 or later then you could also make use of what are called Pivot Table 'Slicers' which give you an even more user friendly way of picking the fields youj want to filter and display.