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

pbj1688

New Member
Joined
May 10, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,019
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 10, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,019
Office Version
  1. 365
Platform
  1. Windows
Do you have any hidden sheets that might have a pivot table on it ?
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,019
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

pbj1688

New Member
Joined
May 10, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,019
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
        .Cells(lPT, 3)).Value _
          = Array(ws.Name, pt.Name, strSD, pt.TableRange1.Address)
 

pbj1688

New Member
Joined
May 10, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
        .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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,019
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,175,587
Messages
5,898,319
Members
434,703
Latest member
azenk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top