combining data from several worksheets in a pivot table

[Colm O'Brien]

I have a workbook with data for sales there is a separate sheet for each
month.
Every time an agent makes a sale he records it using the date he processed
the sale.
the sheet then calculates the week number
i want to produce a pivot table showing sales by agent for each week
however as weeks cross between 2 months i need to be able to include all 12
sheets in the source data for pivot table can this be done.

any ideas please

 

[BrianB]

Yes - by using "Multiple consolidation ranges" when setting up the
table. In my experience this brings more problems than it is worth -
because we do not get the same functionality. Try it with just 2 months
to see what I mean.

Much easier to have everything in one table (adding new month data to
the bottom - with an additional "Month" column if necessary).

 

[Colm O'Brien]

pardon my ignorance Brian but how do i do use "Multiple Consolidation
ranges"

colm