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?
25 Answers
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)
I had this problem. The problem occurred in my case because I had added a table to the Data Model, then had carelessly changed the table name or something about the table. After I deleted my tables from the Data Model, the problem solved itself. After that, you can re-add them to the data model or not, depending on your needs.
In my case, even if you delete every sheet in the workbook the problem still occurs. That is because the problem is in the PowerPivot data model and not in a sheet, per se.
Good luck.
Fix “reference isn’t valid” in Excel
Right click on any other tab sheet in the workbook
Hit Copy from the selection list
A list will come up of what other worksheet tab you might want to copy to
Select any of the tabs
When you do this the problem spreadsheet comes up and shows the problem cells
Delete these and the problem is fixed
I had the same issue, and had refreshed all connections separately successfully, and found that it was a pivot table referencing data which had changed which was no longer valid, so starting point might well be trying to quickly refresh all your pivot tables and see if that highlights the issue.
Excel Reference Not Valid Error I have a pivot that queries an Access Database. I pivot the datasheet that is produced and then split out report pages (Pivot Table Tools//Options). When the Reference Isn't Valid error occurs, I delete the query and select new query and go back to the DB and select the query that I want to import. Then I have to change the data source on the main pivot and each and every report page – which is basically a duplicate pivot that is filtered. I could delete all the report pages, however, I have added data to the top of each page that redoing that would require as much effort as individually changing the data source. The reference must be the query name related to the Access DB, which is also the imported table name.
