pivot table with multiple pivot tables as source

[Jason]

Hi, I have a workbook with 4 tabs with data, each tab is
for a separate week of the month (week 1, week 2, etc).
I've made Pivot tables for each week, and those are place
on different tabs (labeled week1 summary, week2 summary,
etc). However, I would like to build a pivot table to
summarize the month, by creating a pivot table based on
the four other pivot tables. However, if I use the pivot
table wizard, it'll only let me choose one of the four.
Is there a way to summarize the data across all the pivot
tables? If you post a reply, please email me with the
message as well. Thanks a ton!

 

[Debra Dalgleish]

You could create a Pivot Table from multiple consolidation ranges:

1. Choose Data>PivotTable and PivotChart Report
2. Select Multiple consolidation ranges, click Next
3. Select one of the page options, click Next
4. Select each range, and click Add, click Next
5. Select a location for the PivotTable, click Finish

However, you won't get the same pivot table layout that you'd get from a
single range.

For example, if customer is the first column in your data source, the
row heading should show the customer names. If remaining columns are
Units Sold, Product#, Unit Price and Total, the column area will show
each of those headings. You can change the function that's being used by
the data value, but it will use the same function on all these columns.

The Pivot Table would contain some meaningless data, such as sum of
Product# or columns full of zeros if the database columns contain text.
To avoid this, you can rearrange your database columns, and then use
data ranges that only include the columns that you want to total.

If possible, move your data to a single worksheet, and you'll have much
more flexibility in creating the pivot table. As long as the table
includes a date column, you'll be able to filter the data to view each
week's data separately, when required.