I want to create 2 pivot tables from the same data set.

On the first pivot table I want to group by year and quarter and on the other pivot table group by year and month (for example)

Problem is that in excel (2007) creating the first table (year and quarter is fine , when I create the 2nd one (year and month) the first table's grouping is changed to be also year and month.

I did a bit of reading and found out the excel uses the same cache on subsequent pivot tables bases on the same data andI'm guessing that this is what causes this unwanted behaviour.

(Also if the source data changes between creating the 2 pivot tables the second pivot table is based on old data and it's out of date from the start)

Is there any way to force the new pivot table to use its own cache ?



thanks in advance

Perhaps there is a solution at http://www.contextures.com/xlPivot11.html#newcache under "Create New Pivot Cache for Selected Pivot Table"

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Terrific ! . I used the macro you suggested from http://www.contextures.com/xlPivot11.html#newcache and it does exactly what I need .

I was hoping there was a solution without macros (I'm reasonably competent but not totally fluent and for many if not most users using a macro to fix a problem is way beyond their skill level ... do you know if nwever versions ie 2010 / 2013 are the same ? )

I'm sorry, I have no experience with 2013. Glad I could help

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post