Excel 2007: "Reference is not valid" when refreshing pivot table

P

Paul Martin

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
 
P

Paul Martin

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top