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
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