Pivot cache

[DawnE]

Cannot figure out how to control pivot caches. (In Excel 2010 now. Learned them in E2000 and have struggled since E2007.) Researched. Read books and posts. Have played around for a while.

1) See page 41 of Jelen/Alexander book PT Data Crunching. Want to use old wizard (Alt+D then P) to CREATE a 2nd pivot cache. Don't see the old method with wordy message saying I can save memory, etc. So, cannot say "no" to get the 2nd cache. Can someone tell me what I'm doing wrong, please?

2) Also, in one spreadsheet, I knew I had multiple caches. Didn't intend to but ended up with 7. I used some VBA techniques to try to adjust them down to just 3 caches, which is what I wanted for various groupings. I could get a pivot table to read a different cache. Then another. But, at a certain point, the pivot tables would shift which cache they were reading and I'd still not have what I wanted but would have a different mess to fix. Any thoughts on pivots shifting which cache they're reading seemingly without my intervention?

3) And if I'm not the only one with headaches like this when using this fabulous tool (pivot tables), why does MS not make it easier to control things. For example, why can't each Pivot Table have to have a unique "name" and then each cache has a unique "name" and the linkage between the two is simply in a relationship table allowing many to 1.

Thanks for your thoughts.

DawnE

 

[teylyn]

Hi,

1) don't know what's on page 41, but if you use Alt-d-p, then make sure that the top option is selected "Microsoft Excel List or database" > Next > Select the same data source as the other pivot table > Next > Here comes the warning message.

2) hard to comment on without seeing the code

3) Pivot tables DO have unique names. Select the pivot table. On the PivotTable Tools > Options ribbon, the name is displayed in the upper left of the ribbon. You can change it to a different name.

 

[DawnE]

Thank you teylyn.

1) Selecting exact same data source brought up the screen I needed in order to say "no." Excellent. Was just not stepping through the thing.

2) Understood.

3) Regarding pivot table names, they still seem like a weak link to me. I agree with you that there is a name and it shows in the upper left of the ribbon you mention. However, I do not see that it is inherently unique or functional.

3a) For example, when I created a new pivot table (using a new cache from the step above), Excel named it "PivotTable3." However, I already had a "PivotTable3" in this Workbook. So now there are 2 of them.

3b) And, I can simply change the pivot table names to something new or to a name that already exists in the workbook and I don't see any impacts.

3c) And, I cannot "reference" the pivot table names like I can with regular Range Names.

These things said, I am unclear on how the pivot table names add functional value. Or, maybe there is something I'm not understanding.

Thanks!