Automatically summerising data based on multiple categories Excel VBA

[JasonEnsor]

Hi Guys,

I have been tasked with trying to see if i can make a procedure a little more efficient, I know there must be a way of automating most of the procedure but at the moment i am struggling with how to sucessfully do this. I have attached a test workbook. So the details i have been provided are as follows:

Each month data is downloaded in to an excel spreadsheet from our PeopleSoft database, Some sensitive data is removed by hand and the resulting data is added to a new tab based on the current month in an excel document. The workbook contains a summary tab which pulls together information from all of the 12 month tabs in the workbook. the aim of the document is to have a quick view of staff sickness information at a glance, but if further details are needed then it is possible to look at the individual month tabs to get more information.

In the attached workbook you will see that there are several Team Divisions, these are then separated out by Area, this is due to needing the stats for each division and then for a breakdown by team. All calculations are done in the PeopleSoft database. For each team a chart will be created to offer a visual representation, for this i was looking at using a combobox to select the team name or division name to be able to create and display the chart as this is sent to multiple teams who want to look at their particulare teams data.

Currently the my work collegue spends time copying and pasting information from each months sheet in to the summary sheet, they then make a copy of the data in the summary sheet, and remove all data not needed for the chart creation, they then manually create each chart and store it within the summary sheet.

Any thoughts on this would be appreciated. All data in the month tabs are in the same format each month and it's unlikely that teams and divisions will change.


Regards

Jason

 

[SkipVought]

Many of us cannot download data due to company restirctions. If you want to expand the audience from which suggestions/soutions might come, you ought to post example directly her to support your questions.


Again, if it were me, I'd lean toward consolidating your month data into one sheet/table, INCLUDING a column for the year/month, and writing a routine to IMPORT the data from the People Soft workbook via MS Query and APPEND that data to the consolidated data with the corresponding year/month. Using such a consolidated table the summarization would be quite simle!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}