asked on

Create monthly Summary Sheet6 in Excel

Hello Excel Gurus.

I have a simple income/Expenditure balance sheet on an excel sheet called ANNUAL
Essentially there are two sets of columns one for expenditure one for income
Income is in A5: F160 with the date in column A5:A160
Expenditure is in H5:M160 with the date in H5:H160
Dates are in UK format dd-mm-yyyy

I want to create monthly summary sheets, each month on a new tab called Jan, feb, .... dec

Can someone guide me as to how it it possible to create the monthly sheets dynamically from the annual sheet. I assume a filter is required but I'm not sure how to set it up !

Professor J

here are the steps you need to perform to get what you need.

1- select the range and then in Insert Data click Pivot table.
-2 once the pivot table is created then drag the Date to Row label and AMoun to the Value box of pivot table.
3- then click on any cell on the date column of pivot table and then see above in the ribbon , you will see "Pivot Table Tools" Tab and there click on "Options"
4- you will see an icon called "Group Selection" click that and it will open a small window and select "Months" there.
5- your pivot table now will show by Months.
6- now on the pivot table field window, drag the Data Column from "Row Labels" and put it in Report Filter
7- click again on Pivot table Option page and see there shown in screenshot below, "Show Report Filter by Pages" then a small window will show and select that and click ok and it will generate the report for each month in a seperate worksheet.

Brian Pierce

ASKER

I'm really looking for a solution that will automatically create the relevant data on the worksheet for each month so that the monthly worksheets are automatically updated as the data is typed on the main sheet. I don't want to have I don't have to manually refresh a pivot table - is this possible.

Subodh Tiwari (Neeraj)

Upload a sample workbook with your master sheet where you have details of income & expenditure and at least one month sheet and populate the data on the month sheet manually to let us know the layout of the monthly sheet based on the master sheet.

Brian Pierce

ASKER

Attached is the Excel workbook. I have put some sample data on the Annual sheet
Ideally I want the data to replicate itself to a monthly summary sheet

I've created just on monthly sheet for Apr and populated it manually to show the sort of thing I want to happen automatically, there will of course be one tab for each month.

I hope that's clear - any questions please ask!
CurrentAccount.xlsx

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Pierce

ASKER

That's just awesome - thank you very much for your help.

Professor J

Good work Neeraj by going extra mile :)

Subodh Tiwari (Neeraj)