Pivot Table Reference isn't valid error message - source file overwritten

[pbj1688]

Hi, I have two workbooks, one is the source file, one has the pivot table. Source file because corrupt so it was overwritten with a backup, same file name/structure/data. When I try to refresh the table, I get the dreaded:

"Reference isn't valid" and "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

The Data Source currently references the table name. I've gone into Change Data Source to select the new file and data range but get the same error. I've pasted the original path referencing the table name, same error. I've confirmed that the table name exists in the name manager in the "new" source file.

I assume I need to create a new association the to table in the new file but can't seem to just select it. Tried manually typing the table name, doesn't work.

Any suggestions on what else I can change/update without recreating the whole pivot table? I'm sure I'm just missing something. Thank you so much!

 

[Alex Blakenburg]

Firstly do you have a backup copy of your pivot table workbook ? (may or may not need it later )
Using your new data workbook, if you try to create a new pivot from it does it complain about any invalid headings ?

On the existing pivot if you go to the grand total will it let you double click on it and explode / drill down to the data ? If so compare the field names with the field names in the new datasource (possibly using a match formula or "=" formula so you can be sure they are an exact match.)

What happens if you use the dialog box of the Change Data source and select only a part of the new table ie not the whole table so that it does not pick up the table name and uses a normal range ?

 

[pbj1688]

Yes to back up
Yes, I can create a new pivot from new data workbook, no errors
Yes, if I double click on grand total of existing pivot, it drills down to the data
All data fields are a match from drill down and data source, using match formula to validate
I can use the dialog box to select a portion of the new data and it will refresh.
It looks like if I just select refresh, it seems to update fine. If I select refresh all, it gives the same error. I've gone through the other pivot tables in the work and refresh works on those as well. What else can I check?

thanks for responding!!

 

[Alex Blakenburg]

Do you have any hidden sheets that might have a pivot table on it ?

 

[Alex Blakenburg]

If you still can't find it try running Debra Dalgleish's (Contextures) macro that you can find here:
Excel Pivot Table Data Source Macros List Info VBA
It will create a new sheet in your workbook listing the Pivot tables and their data sources. Look for any DataSources that look inconsistent.

 

[pbj1688]

If you still can't find it try running Debra Dalgleish's (Contextures) macro that you can find here:
Excel Pivot Table Data Source Macros List Info VBA
It will create a new sheet in your workbook listing the Pivot tables and their data sources. Look for any DataSources that look inconsistent.

thank you! I will try that. I looked at all the hidden files and the pivot tables worked with Refresh but not Refresh All. Truly appreciate the tips. thank you.

 

[Alex Blakenburg]

If you can't identify which pivot is which from the output of Debra's macro (it gives sheet name and pivot table name) add this to your output.
pt.TableRange1.Address
eg

        .Cells(lPT, 3)).Value _
          = Array(ws.Name, pt.Name, strSD, pt.TableRange1.Address)

 

[pbj1688]

If you can't identify which pivot is which from the output of Debra's macro (it gives sheet name and pivot table name) add this to your output.
pt.TableRange1.Address
eg

        .Cells(lPT, 3)).Value _
          = Array(ws.Name, pt.Name, strSD, pt.TableRange1.Address)

This is a very handy tool, thank you for sharing Debra's macro.! The data sources all looked valid, especially since refreshing each table individually worked. I still don't know why Refresh All doesn't work but there are only a handful, thankfully, so i can just refresh individually. I will mark this as solved since it's probably an error on my end. Thank you!!

 

[Alex Blakenburg]

Were you able to identify all the pivot tables produced by the macro ie is it possible there is one or more pivot table that you have not refreshed manually and hence could be caught up in the refresh all ?
Also if you open the Power Query navigation pane are there any queries in there, also in Power Pivot do you have anything in the Datamodel ?

If there is anyway you can desensitive the data and share your spreadsheet via a sharing platform such as Google drive, drop box etc, I am happy to take a look.