Pivot table data source not changing when file is renamed

[andydala]

I have a spreadsheet with various pivot tables, all of which reference the same data tab within the workbook. There are no links to external data anywhere within the workbook. I have been using this file for years, and every month I roll it forward, using the exact same procedures as I have for years:

-Copy the file
-Paste the file to another folder (archiving it)
-Rename the file for the new month

What should happen, is that the pivot tables continue to reference the data sheet. What is happening, is that when I open the renamed file, the old file name is embedded in pivot table data reference.

After renaming the file to "OCT", the pivot table reference should be unaffected and continue to be: TB!$A:$E

However, after the rename, the pivot table reference in the renamed file becomes: '[Consol Pack - SEP 2016.xlsx]TB'!$A:$E

As a result, I have to go into every pivot table and clear out the old file name, so that it refers to the data within the renamed workbook. Every other workbook I use with pivot tables behaves normally. But this one workbook has started misbehaving. I am the only person who works on the file, and I have been working on it exactly the same for years. I can't recreate the problem with other workbooks, so it seems to be something confined to this one workbook.

If I have the workbook open, and I file save as, and give it a new name, and then go look immediately at the pivot table data source, it has changed to add the reference to the name of the file before the save as. So rename and save as both cause the problem, and I can't even think of a workaround.

If that rings any bells, I could really use some help...

Also, I'm sure the first time I saw this happen was after I upgraded to Excel 2013. Every version of this file which I updated on 2013 version behaves like this. Now that I'm back on 2010 version, the files May - Sep all still behave erratically. However, if I go back to the April file, which was never touched by 2013 version, it behaves normally when handled with the 2010 version I now have installed.

 

[andydala]

Also, in 2013 version after the file rename, which caused the pivot table reference to become '[Consol Pack - SEP 2016.xlsx]TB'!$A:$E, it would give an error either when the book was opened, or when you refreshed pivot tables, because of course, there is not file with "SEP" in the name anymore, so it can't find the data. Now that I have 2010 version back, when I open the file, and refresh the pivot tables, it gives no error. It just processes for a few seconds, like it is updating the pivot tables, and then finishes. But the file Consol Pack - SEP 2016.xlsx doesn't exist, so where is it looking and finding data that it is refreshing while it churns for those few seconds? It's not pulling from the TB tab of the new book, checked that.

 

[SpillerBD]

I think you want to Open the file and then use the SaveAs to save it to the Archive Location.

I have seen the behavior described but not often enough or in obtrusive ways.
The cases (which I haven't paid close attention to) that I have had may be from copying the file from/to network folders in which I am not the owner of and when the ownership changes it may be creating that issue.
I have played around with copying with PT and a file in your descriptions but haven't actually been able to recreate either. (Excel 2013-32bit, Win7-32-Bit)

 

[SpillerBD]

... maybe the changes MS implemented for the Data Model capabilities in 2013 are to blame for your experience.

For me Work is 2013 and Home is 2016! I love Pivot Tables and use them for almost everything. However, I would check out Mike Girvin's YouTube channel on some of the PowerQuery features (Get and Transform in 2016.)

 

[andydala]

Thanks - do you think by chance excel could behave differently if you access the file through a network drive mapped to a drive letter? I access pivot tables mainly through a shortcut that looks like this:

\\fs01\Department\Accounting\Journal Entries

where fs01 is the network drive

but the file path for the file with the pivot table issue is

G:\Monthly\Consol

where G is a mapping to \\fs01\Department\Accounting