I have an couple of interesting problems for which I have been unable to find any tips on the net.
1 Pivot table based on multiple ranges (same Format) over a number of spreadsheets
Initially I thought that this would be possible using consolidate pivot tables, however this tends to consolidate only the value / amount fields and even when the pivots reworked will not give a standard pivot table. If you then drill down data (doubler click on total) the data is not in the same format as source.
I would welcome any ideas i.e. name ranges covering multiple sheets, re working of consolidated pivot table drill down data or even a simple macro to copy and paste data forma each of multiple source sheets onto one consolidated sheet.
I have even considered putting the individual sheets into Access to produce the pivot table.
Once again I find it hard to believe that this problem has not arisen before and a solution found. I certainly believe that this is the sort of solution to a Pivot table challenge that a number of people would find extremely useful.
2. Update links to external spreadsheets automatically without confirmation ( Click OK to update links)
The source data for this proposed Pivot table is based on a number of templates pulling data linked to data files downloaded from a mainframe report ( Peoplesoft –Excel query export ).
Is there a VBA command / Macro to upload and update all data and template spreadsheets with minimal or no further interaction on my part?
Many thanks in advance for your help with these queries
1 Pivot table based on multiple ranges (same Format) over a number of spreadsheets
Initially I thought that this would be possible using consolidate pivot tables, however this tends to consolidate only the value / amount fields and even when the pivots reworked will not give a standard pivot table. If you then drill down data (doubler click on total) the data is not in the same format as source.
I would welcome any ideas i.e. name ranges covering multiple sheets, re working of consolidated pivot table drill down data or even a simple macro to copy and paste data forma each of multiple source sheets onto one consolidated sheet.
I have even considered putting the individual sheets into Access to produce the pivot table.
Once again I find it hard to believe that this problem has not arisen before and a solution found. I certainly believe that this is the sort of solution to a Pivot table challenge that a number of people would find extremely useful.
2. Update links to external spreadsheets automatically without confirmation ( Click OK to update links)
The source data for this proposed Pivot table is based on a number of templates pulling data linked to data files downloaded from a mainframe report ( Peoplesoft –Excel query export ).
Is there a VBA command / Macro to upload and update all data and template spreadsheets with minimal or no further interaction on my part?
Many thanks in advance for your help with these queries
