Consolidated Pivot Table for Year End Expenses

[jshu39]

I am using Excel 2010 for Windows on Windows 7

I have a Workbook that has a Worksheet for each month, on each Worksheet is a Table where I enter and sort my expenses that are taken from several budget lines, and at the bottom of each Worksheet I have a PivotTable that has the Expense Table above it as its Data Source.

What I would like to do is to have a final Worksheet showing my yearly expenses that gets it's data from either 1. All 12 Tables from the 12 monthly Worksheets or 2. All 12 PivotTables from the 12 monthly Worksheets. This would make it easier to sort and confirm expenses for the year instead of just month per month.

Thanks for your help.

 

[Comfy]

You could create a new Data Source using MSQuery.

Combine all of your Tables using UNION ALL, then analyse this.

Here's an example I put together using three different tables:

SELECT `Sheet1$`.Date, `Sheet1$`.Amount
FROM `H:\Excel\MrExcel\Answer.xlsx`.`Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Amount > 0
UNION ALL
SELECT `Sheet2$`.Date, `Sheet2$`.Amount
FROM `H:\Excel\MrExcel\Answer.xlsx`.`Sheet2$` `Sheet2$`
WHERE `Sheet2$`.Amount > 0
SELECT `Sheet3$`.Date, `Sheet3$`.Amount
FROM `H:\Excel\MrExcel\Answer.xlsx`.`Sheet3$` `Sheet3$`
WHERE `Sheet3$`.Amount > 0

Apologies, but my SQL knowledge is limited at best, the where statement is only there as I got blank rows. There is probably/definitely a way to do that better.

Edit: From my experience MS Query is very picky about the order in which you do things.

You have to add all the tables to MS Query and then add your SQL code.

 

[Comfy]

IS NOT NULL is what I was looking for:

SELECT `Sheet1$`.Date, `Sheet1$`.Amount
FROM `H:\Excel\MrExcel\Answer.xlsx`.`Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Date IS NOT NULL
UNION ALL
SELECT `Sheet2$`.Date, `Sheet2$`.Amount
FROM `H:\Excel\MrExcel\Answer.xlsx`.`Sheet2$` `Sheet2$`
WHERE `Sheet2$`.Date IS NOT NULL
UNION ALL
SELECT `Sheet3$`.Date, `Sheet3$`.Amount
FROM `H:\Excel\MrExcel\Answer.xlsx`.`Sheet3$` `Sheet3$`
WHERE `Sheet3$`.Date IS NOT NULL

 

[jshu39]

Thanks I'll give it a try