Excel – Mysterious “Reference not valid”

microsoft excelmicrosoft-excel-2010

Error dialog: Reference not valid

I have a big sheet with 30+ pivot tables made from the same 3 named tables.

Every time i run refresh all I get the Reference Not Valid-error.

I have no idea which pivot table is causing the error – i have tried updating them one by one and none of them give out this error when being updated.

The result (a big matrix using GETPIVOTDATA to pick out values from each of the pivot tables) looks alright so have not been able to find the error that way.

Is it possible to find the offending pivot table?

Best Answer

Facing the same problem, my solution was (considering big number of sheets, and possible multiple pivot / data tables per sheet):

  • create a back-up of the file
  • delete sheets one by one and run "refresh all" after each to see when the problem disappears
  • when the sheet with the issue is identified refresh all the pivot tables individually (actually I've also deleted the pivot tables, as the one with mistake was small and hidden at the end)
Related Question