Pivot Table "Reference is not valid" Error

Question

I am a fairly experienced pivot table user and create complicated models with many pivot tables.  When I "Refresh All", I occasionally get a "Reference is not valid" error (see screenshot below).  I can track this problem down by refreshing each pivot table individually, but this is a time consuming waste of effort when the workbook contains many (50+) pivot tables.  Is there an easier way to find and correct the error? 

Answer 1

Hi,

I don't think there is.  I even used the Inquire feature of Excel 2013 but not even that showed any external links.

Answer 2

Try this link instead.

https://onedrive.live.com/redir?resid=510F02E64CF4576D!125&authkey=!ACwWs7rqRyyfLNw&ithint=file%2c.xlsx

Answer 3

HI,

I get a message saying that the file cannot be opened because it is more then 5 MB in size.  Try some other service such as Google Drive

Answer 4

Try downloading from the following link.  Thanks again.

https://  ... [ LINK REMOVED ]

Answer 5

Try downloading from the following link.  Thanks again.

https://  ... [ LINK REMOVED ]

I was able to find the source of the "Reference is not valid" error.  A worksheet, containing both source data and a pivot table, was copied into the workbook.  The pivot table remained linked to the worksheet it was copied from (a different workbook file) rather than to the source data on the new worksheet that was created as the copy.  So, immediate problem has been solved.  The bigger question remains, though:  is there an easy method to track down a "Reference is not valid" error in a workbook with dozens of pivot tables?