Excel 2007: "Reference is not valid" when refreshing pivot table
in [Programming]
|
Prev: how do i use analytical hierarchy process in excel?
Next: Cell Format Function "NumberFormatLocal" in VBA
From: Paul Martin on 3 Jan 2010 19:57 Hi all I have a pivot table that works fine in Excel 2003. I've created an Excel 2007 version and some code fails because (in VBA) "The PivotTable report was saved without the underlying data". Yet, when I manually try to refresh the pivot table, I get a message saying "Reference is not valid". I've had a look at the source data, but there doesn't appear to be any issues with that. Can anyone suggest what the error might be? BTW, I'm not sure if I've cross-posted correctly (excel.misc & excel.programming), so apologies if this causes any problems. Thanks in advance Paul Martin Melbourne, Australia
From: Paul Martin on 4 Jan 2010 00:14 FWIW, the data source for the pivot table is a dynamic range
From: Paul Martin on 4 Jan 2010 21:47
Perhaps someone can shed more light on this, but I ascertained that the problem was with a dynamic range that works in Excel 2003 but not in Excel 2007. The formula looks like this: =OFFSET(INDIRECT(ADDRESS(1, 1, , , "DataDaily")), 0, 0, COUNTA(INDIRECT("DataDaily!" & ADDRESS(1, 1) & ":" & ADDRESS(65536, 1))), COUNTA(INDIRECT("DataDaily!" & ADDRESS(1, 1) & ":" & ADDRESS(1, 256)))) Which I've changed to: =OFFSET(DataDaily!$A$1, 0, 0, COUNTA(DataDaily!$A:$A), COUNTA (DataDaily!$1:$1)) The reason for the original formula was to avoid issues when the range was deleted. Obviously the second formula is simpler, and I'll have to workaround deletion issues. Anyway, if anyone has anything to add, it'd be good to understand why this isn't working in XL07. |