Pivot Table Problem

[SteveG]

I have a workbook that contains 10 separate sheets. Each sheet has data that is arranged in 3 columns with the same column labels and same type of data. I want to create a pivot table that will consolidate all 10 of the data sets. I am able to do this using the multiple range functionality but it does not produce the results I am looking for. I have tried changing the layout multiple ways to no avail. It will if I do one pivot table for each but then I would need to merge all of the individual Pivot Tables which I can not seem to do. What I want is this:

Columns a,b,c of each sheet have the headers Work Type, Status and Count. The pivot table I create will result in the sum of the count for each work type and status. I want to be able to deselect or select any work types or statuses. So the table will have Worktype, Status and the corresponding sum of the counts reading left to right. For some reason I can not reporoduce this using the multiple consolidation range option. Does anyone know how I might be able to do this? Any help is very much appreciated!


Regards,

Steve

[Debra Dalgleish]

The layout for a pivot table based on multiple consolidation ranges is
very limited. If possible, store all the data on one sheet, with an
extra column to indicate the source, e.g. Region.

Then create a pivot table from the single list, and use filters on the
list when you want to view or print the data for a single source.

SteveG wrote:
> I have a workbook that contains 10 separate sheets. Each sheet has data
> that is arranged in 3 columns with the same column labels and same type
> of data. I want to create a pivot table that will consolidate all 10
> of the data sets. I am able to do this using the multiple range
> functionality but it does not produce the results I am looking for. I
> have tried changing the layout multiple ways to no avail. It will if I
> do one pivot table for each but then I would need to merge all of the
> individual Pivot Tables which I can not seem to do. What I want is
> this:
>
> Columns a,b,c of each sheet have the headers Work Type, Status and
> Count. The pivot table I create will result in the sum of the count
> for each work type and status. I want to be able to deselect or select
> any work types or statuses. So the table will have Worktype, Status and
> the corresponding sum of the counts reading left to right. For some
> reason I can not reporoduce this using the multiple consolidation range
> option. Does anyone know how I might be able to do this? Any help is
> very much appreciated!
>
>
> Regards,
>
> Steve
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html