Custom Views and Pivot Table Efficiency

[tcardwell]

Hello All,

That time of the year(budget time). In order to give department heads something to base next years budget on, I have an Excel spreadsheet that contains all transactions for all departments for a 12 month period, it's only 17,000 rows. I have developed a pivot table that summarizes by entity, by cost center(department), by account and by supplier/vendor. I run a particular set of data for above criteria and then copy the result to a new worksheet in order to give to that particular department manager. After doing about 20 of these views the server is starting to choke on the size of the file that has been created. Is there an accepted practice on how to copy views of a pivot table without copying the entire 17,000 row source table to each view? Using XL2007.

Thinking I've got to be missing something,

Thanks, TC

 

[Jerry Sullivan]

Hi TC,

Are you pasting functioning PivotTables to each sheet, or just pasting values and formats?

Either way, unless you've got a large percentage of your 17,000 rows of data displayed, that shouldn't be causing the file size to grow to the point of choking the server.

When you make a copy of an entire PivotTable both the original and its copy share the same PivotCache which is where your 17,000 row dataset is stored. If you then change the datasource it would create and use a new PivotCache, but it doesn't sound like you are doing that.

It's possible that you have some other baggage that is being copied each time you create a new worksheet and paste a copy of the PivotTable.

 

[Fazza]

Is there a single field, or could you add one, that can be used to break up the full dataset into the required subset for each department manager?

Then either use the 'show pages' option within the pivot table, or some VBA to split out the data into sub-files. There will be plenty of old posts that do that. DataPig website used to have an add-in (or code, I can't remember) for it (Excel Explosion) or Ron De Bruin's web-site has some code. Other sites do too. Just google.

How many different department managers are there?

HTH. regads

 

[tcardwell]

Hi Jerry,

The way I created each sheet from the pt is to click top left corner to select all then right click to copy, navigate to sheet I want to copy to and then hit ctrl-v. In the sheet/view created from the pt I can double-click on a cell and a new sheet gets created that is a view of just the records that make up the balance in that cell(I then delete these results). Sounds like I should just be pasting values and formats?


Thanks, TC

 

[tcardwell]

Well, I did not know about the 'show pages' option for pivot tables. I tried it out and it worked great, thanks! Right now, I have two criteria as report filters and I noticed that 'show pages' only seems to work with one criteria, which is ok, I can work with that. There are about 12-15 department managers which is one of the criteria, the other one being the entity. I take it that the add-ins you referenced are able to make 'show pages' work with more than one criteria? That would be nice, I am going to look into these right away.

Thanks Fazza much appreciated!