Pivot table from data of same kind coming from two different sheets

[robertomd]

Hi all,

I have no problem when it comes to create a pivot table using data from a single sheet that includes, say, columns Date+Category+Amount; so I can easily get for example a summary pivot table with dates by month on one axis, categories on the other, and sum of the amount on the body. Nothing strange here.

But my problem comes when I want to use more than one sheet as source data. The sheets have the same columns (say Date, Category, Amount), but correspond to different data sets so I keep them in separate sheets. I'd like to have a pivot table that allows me to get an aggregation of every sheet on the chosen sheets. For example, the same result of the first example (date grouped by month on one axis, category on the other, and the sum of amounts for all the wished sheets. Is there an easy way to do this?

Thank you in advance!

 

[Jerome]

It may be overkill (for small data sets, I'd copy and paste tables together), but you can create a second file and create a data connection to your original file. Then use PowerPivot to create a connection to the table. You can then edit the PowerPivot link and combine your two tables into one (and run a pivot).


Example:

http://www.contextures.com/PowerPivot-Identical-Excel-Files.html

 

[bobhc]

Good day robertomd


PivotTables can automatically combine data from multiple data sources. The PivotTable tool for accomplishing this task is not included in the ribbon – you will find it by Customizing the Quick Access Tool Bar and searching the “Commands Not Shown in the Ribbon” tab to find the PivotTable and PivotChart Wizard Option.

 

[robertomd]

Jerome, bobhc, thank you very much for your answers. It looks then, according to your answers, that data must be combined prior to be used by a pivot table. Instead of creating a second file, I'll try creating an additional sheet with all the results combined, and then do the pivot table on this sheet.


Thanks!

Regards