K
katie_c
Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
I have a multiple sheet workbook with lots of data that I'm trying to use as a template workbook. I created a data tab (Sheet1) with multiple formulas to collapse other data tabs in the workbook. I setup a "pivotdata" dynamic range using the offset function for data on Sheet1, so that when I add extra records to the data that dynamic range updates automatically.
On Sheet2, I created multiple pivot tables that reference "pivotdata". The first table references the dynamic range, then all of the other pivot tables use that first table as their base (to save memory and refresh all when I refresh the first one).
My issue is that when I change the data on Sheet1 and refresh the pivot tables, the spreadsheet crashes. On further investigation, it seems that the pivot tables are not allowing the use of the dynamic range "pivotdata". When I try to manually update individual tables on Sheet2, I receive "PivotTable invalid data".
Another oddity is that when I manually define the range for the pivot tables, they work, even if they match exactly to "pivotdata" dynamic range - which returns invalid. And if, after creating a valid pivot table, I go back in and define the dynamic range, they work again.
Could I just be running up against a limit of calculations/named ranges with my pivot tables?
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
I have a multiple sheet workbook with lots of data that I'm trying to use as a template workbook. I created a data tab (Sheet1) with multiple formulas to collapse other data tabs in the workbook. I setup a "pivotdata" dynamic range using the offset function for data on Sheet1, so that when I add extra records to the data that dynamic range updates automatically.
On Sheet2, I created multiple pivot tables that reference "pivotdata". The first table references the dynamic range, then all of the other pivot tables use that first table as their base (to save memory and refresh all when I refresh the first one).
My issue is that when I change the data on Sheet1 and refresh the pivot tables, the spreadsheet crashes. On further investigation, it seems that the pivot tables are not allowing the use of the dynamic range "pivotdata". When I try to manually update individual tables on Sheet2, I receive "PivotTable invalid data".
Another oddity is that when I manually define the range for the pivot tables, they work, even if they match exactly to "pivotdata" dynamic range - which returns invalid. And if, after creating a valid pivot table, I go back in and define the dynamic range, they work again.
Could I just be running up against a limit of calculations/named ranges with my pivot tables?