Hello.

I have a .XLSX file, that was created long time ago (I don't even know in which Office version, but definitely not 2013), and maybe even was a .XLS file at first.

So it's a 4 MB file with 16 Sheets and 8 Pivot Tables.

All of the Pivot Tables use other sheets from the same file as Data Source.

Data Source for some of them look like this: 'Sheet3'!$A:$E

Everything is fine when I save the file, and open it from saved file. 

But as soon as I try to move the file elsewhere, or rename it, or email it - all Data Source paths change to something like this: '\Users\Sergii_Litnevskyi\Desktop\New folder\[FileName.xlsx]Sheet3'!$A:$E

And it happens with all Pivot Tables. The problem is that it links to an old file path, where the file does not exist anymore. And it links to an external file, which is not what I want.

If I Save As and select different path and filename - then it works fine. So it's a workaround for renaming and moving files, but not for sending them to other persons.

I've read some threads, and people recommend disabling "Save external link values", but it does not help. It is already turned off in my office, but it keeps acting weird. 

So what I need is: Save the file, close it, rename it, move it to other place, send it over email as attachment. And then I want to have the same Data Source path in my PivotTables as I had before I saved the file. How can I do it?

My Office version: Microsoft Excel 2013 (15.0.4454.1503) MSO (15.0.4517.1005) 32-bit

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

• Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Tuesday, August 20, 2013 9:09 AM 123

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

• Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Tuesday, August 20, 2013 9:09 AM 123

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

• Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Tuesday, August 20, 2013 9:09 AM 123

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

• Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Tuesday, August 20, 2013 9:09 AM 123

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

• Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Tuesday, August 20, 2013 9:09 AM 123

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

• Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Tuesday, August 20, 2013 9:09 AM 123

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

I am pretty sure that I don't have any external links in the document.

However, even if I did - why would it change Data Source path for all of the Pivot Tables, when I did not request it?

I tried zipping it and sending to other person over email, but he got the file with changed data source paths.

I can even record a short video to show what happens.

Actually, I just did it. You can see the video here: http://screencast.com/t/qMBild3ck9b

It is rather big - 23.8 MB.

Let me explain what I showed there:

I opened my original file. I showed that there are Pivot Tables, whose Data Sources are in the same file, on various other sheets.

I showed this for all of the Pivot Tables in the document.

I saved the file using Save As in a different folder and under a different name (TEST.xlsx).

I then opened that saved file to show you that it is fine, and the Data Source path for one of the Pivot Tables is the same as it was in original file. It is the same for all of the other Pivot Tables.

Then I closed, and simply renamed the file to TEST123.xlsx.

Opened it, and first thing wrong - Security warning.

Then I got Cannot open PivotTable source file . messages. And, as I showed, now all Data Source paths have been changed to full paths of the file, that was created by Save As (TEST.xlsx) from original file.

• Proposed as answer by papawlik Tuesday, September 01, 2015 5:00 PM
• Unproposed as answer by papawlik Tuesday, September 01, 2015 5:00 PM

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

• Edited by George.Zhao CHNMicrosoft contingent staff, Moderator Tuesday, August 20, 2013 9:09 AM 123

Hi,

This problem has been mentioned before, sadly without any real answer to it.  I am still confused why are there so few complains about this serious problem. Probably because files created in earlier versions of excel are not affected.

Nevertheless,  I have found a way around it: 

What is seems to be working is using File --> Save as.. when copying/moving/transferring/renaming files.

When I try copying/moving/transferring/renaming the through the Windows Explorer without opening it via Right Click & Copy / Ctrl+C / Drag & Drop etc, then the links change to absolute, thus reading from initial file. 

The same issue in mentioned in another relative thread under the title:

"Excel 2013 - absolute and relative links problem" (Sorry, my account is unable to paste links)

Hi,

According to your description, I suppose the issue may be caused by some reason.

Do you link the outside data source?

I think if the file moves the file elsewhere, or renames, or email, data source paths cant be change.

But, your data source paths add the absolute path.

Do you link the outside data source?

I recommend you zip the file and send it as Email attachment.

If the issue exists, you may save as it in a new name and test it in another computer.

Regards,

George Zhao
TechNet Community Support

I am pretty sure that I don't have any external links in the document.

However, even if I did - why would it change Data Source path for all of the Pivot Tables, when I did not request it?

I tried zipping it and sending to other person over email, but he got the file with changed data source paths.

I can even record a short video to show what happens.

Actually, I just did it. You can see the video here: http://screencast.com/t/qMBild3ck9b

It is rather big - 23.8 MB.

Let me explain what I showed there:

I opened my original file. I showed that there are Pivot Tables, whose Data Sources are in the same file, on various other sheets.

I showed this for all of the Pivot Tables in the document.

I saved the file using Save As in a different folder and under a different name (TEST.xlsx).

I then opened that saved file to show you that it is fine, and the Data Source path for one of the Pivot Tables is the same as it was in original file. It is the same for all of the other Pivot Tables.

Then I closed, and simply renamed the file to TEST123.xlsx.

Opened it, and first thing wrong - Security warning.

Then I got Cannot open PivotTable source file . messages. And, as I showed, now all Data Source paths have been changed to full paths of the file, that was created by Save As (TEST.xlsx) from original file.

In addition, I must add the problem of shared network files.

For example, in an office network, if someone uses a map network drive letter, that path then becomes absolute for everyone else.

Say I name my COMMON folder with the letter K.

So the files I create will have a path:  K:\Work\Documents\File

That means that the file is useless for anybody with access on the same folder unless he/she uses the same letter to create a map network drive.

If another user uses a different letter to map the same COMMON folder, say J.

Then the files will have a path:  J:\Work\Documents\File

And the problem is there! Imagine this with multiple co-workers..

The same thing happens in dropbox since the path usually changes from one computer to another.

Say,

OFFICE COMPUTER: C:\Users\officepc\Dropbox

HOME COMPUTER:   C:\Users\homepc\Dropbox

And the problem is there (again)!!

-Patience Daniel-son.. the solution will come... hopefully before we are forced to go back to the previous version..

Anyone anything?

This seems to be a bug. As far as I remember this is not reproducible in pre-2013 Office versions.

Looks like a bug to me.  I can reproduce.  Any xlsx files when linked keep their links as absolute paths when copied or moved.

The behavior does NOT happen in Office 2010, it also works in 2013 if the file is a .xls (compatibility mode).  I am at a loss for a fix.  I think the fix has to come from Microsoft.  Only work around I have found is to not use the .xlsx format when working with linked files, in 2013.

I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

Hope these additional clues can help find a solution.

• Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
• Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM

I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

Hope these additional clues can help find a solution.

• Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
• Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM

I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

Hope these additional clues can help find a solution.

• Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
• Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM

I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

Hope these additional clues can help find a solution.

• Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
• Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM

I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

Hope these additional clues can help find a solution.

• Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
• Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM

I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

Hope these additional clues can help find a solution.

• Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
• Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM

I do have the same exact problem (very well described above). Apparently this started to   happen after installing an add-in and enabling the developers toolbar, in fact excel was working fine before. I'm not 100% sure because I noticed the issue a couple of weeks after installing the plug-in, so I'm not sure if something else had changed in the meanwhile.

I've also removed the developer toolbar and disabled the add-ins, but the problem still is there. Updated Office with no effect as well.

Hope these additional clues can help find a solution.

• Edited by fabio p to Tuesday, March 18, 2014 9:14 PM
• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:53 AM
• Unproposed as answer by fabio p to Wednesday, March 19, 2014 11:54 AM

I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM

I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM

I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM

I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM

I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM

I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM

I have actually figured out that the problem is not there if I use the data model when creating the pivot table (flagging Add  this data to the data model when creating the pivot table). In that case I can move the file around and rename it without Excel changing references from pointing to internal data to absolute path of a file (that in case of a rename, does no longer exists).

Still need to figure out how to convert a pivot based on a data range into a pivot based on data model.

this workaround introduce compatibility issues with older versions of excel (in 2007 the file opens in read only and the pivot cannot be edited)

• Proposed as answer by fabio p to Wednesday, March 19, 2014 11:59 AM

• Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
• Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
• Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
• Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM

• Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
• Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
• Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
• Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM

• Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
• Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
• Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
• Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM

• Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
• Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
• Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
• Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM

• Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
• Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
• Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
• Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM

• Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
• Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
• Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
• Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM

• Edited by PL_FL Tuesday, April 22, 2014 7:59 PM
• Proposed as answer by BrainE Monday, June 16, 2014 6:38 PM
• Unproposed as answer by BrainE Monday, June 16, 2014 6:39 PM
• Proposed as answer by Rob Quinn Friday, September 26, 2014 6:59 PM

I didn't seen any real resolution that was simple on this thread so I'm posting. This method seemed to work for me.  

In Windows Explorer, right-click on the file and select copy.  Then Paste into the same folder.  A new file will be created as 'filename COPY'.  You can then just re-edit the filename as desired there (ie. with a new date).  You can then update whatever data tables you have and the pivot tables will be referencing those tables internal to that document and not the old file.  

Thank goodness that worked!

Hope it helps anyone else out there!

Phil

I know it's a bit old topic, but here is  solution:

Open the thisWorkbook module in VBA, then:

Private Sub Workbook_Open()
Worksheets(PivotSheetName).PivotTables(PivotTableName).ChangePivotCache _    
ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, SourceData:=Worksheets(SourceSheet).Range(PivotSource))
End Sub

Whenever you open your file, this code will rewrite the pivot source to the actual.

It works for me.

Tamas

The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

But one thing I did verify, as you mentioned using "xls" solves the problem!

This definitely is a bug.

• Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
• Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM

The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

But one thing I did verify, as you mentioned using "xls" solves the problem!

This definitely is a bug.

• Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
• Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM

The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

But one thing I did verify, as you mentioned using "xls" solves the problem!

This definitely is a bug.

• Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
• Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM

The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

But one thing I did verify, as you mentioned using "xls" solves the problem!

This definitely is a bug.

• Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
• Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM

The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

But one thing I did verify, as you mentioned using "xls" solves the problem!

This definitely is a bug.

• Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
• Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM

The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

But one thing I did verify, as you mentioned using "xls" solves the problem!

This definitely is a bug.

• Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
• Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM

"Save As" used to work.

But i just "Saved As" a sheet I've been working on for a while and now Excel 2013 has gone back to saving the entire path. 

How do we get Microsoft to fix this once and for all?

"Save As" used to work but now doesn't work. So I tried your Macro on a test Sheet with a Pivot Table. I get "Subscript out of range."

Does your solution require manually entering the PivotSheetName and PivotTableName  in the Macro? Or is there some other missing piece in your code? 

The Problem seemed to be fixed by the "Save As" solution mentioned above, but yesterday, the problem returnde. I don't know if Microsoft Excel had an update!

But one thing I did verify, as you mentioned using "xls" solves the problem!

This definitely is a bug.

• Proposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM
• Unproposed as answer by X.Romea Wednesday, January 21, 2015 4:56 PM

Hello everyone, I think I might have found a solution to this particular issue.

Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

After going mad for a couple of hours, I find out some Excel options that solve this issue.

Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

Then I unchek "Update link to others documents" and "Save external link values".

Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

It seems to do the trick.

Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

• Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text

Hello everyone, I think I might have found a solution to this particular issue.

Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

After going mad for a couple of hours, I find out some Excel options that solve this issue.

Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

Then I unchek "Update link to others documents" and "Save external link values".

Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

It seems to do the trick.

Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

• Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text

Hello everyone, I think I might have found a solution to this particular issue.

Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

After going mad for a couple of hours, I find out some Excel options that solve this issue.

Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

Then I unchek "Update link to others documents" and "Save external link values".

Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

It seems to do the trick.

Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

• Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text

Hello everyone, I think I might have found a solution to this particular issue.

Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

After going mad for a couple of hours, I find out some Excel options that solve this issue.

Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

Then I unchek "Update link to others documents" and "Save external link values".

Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

It seems to do the trick.

Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

• Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text

Hello everyone, I think I might have found a solution to this particular issue.

Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

After going mad for a couple of hours, I find out some Excel options that solve this issue.

Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

Then I unchek "Update link to others documents" and "Save external link values".

Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

It seems to do the trick.

Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

• Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text

Hello everyone, I think I might have found a solution to this particular issue.

Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

After going mad for a couple of hours, I find out some Excel options that solve this issue.

Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

Then I unchek "Update link to others documents" and "Save external link values".

Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

It seems to do the trick.

Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

• Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text

Hello everyone, I think I might have found a solution to this particular issue.

Here's my problem : I make a template file with pivot tables and their data sources in the same workbook but in different worksheets.

When I check the data source of every pivot table, Excel shows the good reference : [WORKSHEET_NAME]![CELL_RANGE]. I code an C# web application that makes a copy of this template file and update the data sources. Then this copy is available for download to users.

The problem is when the user opens the copy, each pivot table is not update with the updated data because the data source reference is set as : [TEMPLATE_WORKBOOK_FILENAME][WORKSHEET_NAME]![CELL_RANGE]. Although the worksheet name and the cell range are correct, Excel manages to save the filename in the data source reference !

After going mad for a couple of hours, I find out some Excel options that solve this issue.

Sorry if the names of these options don't match with your Excel version, I use a French version (Excel 2013) and translate as best as I could the names.

First, I open the template workbook. in the Excel Options > Advanced > When calculating this workbook, I select the template workbook.

Then I unchek "Update link to others documents" and "Save external link values".

Afterwards, on each pivot table options > Data tab, I uncheck "Save source data with file" (as the data are in the same file but in a different worksheet). I also check "Refresh data when opening the file". ANd finally save the template workbook.

It seems to do the trick.

Now each time an user downloads a copy of the template orkbook with updated data, all pivot tables use the right data sources.

However, every time I open the template workbook, Excel automatically set the options "Update link to others documents" and "Save source data with file" to true. So I have to redo these manipulations every time I modify the template workbook. Hope this helps !

• Edited by X.Romea Thursday, January 22, 2015 8:13 AM format text

Thank you X Romea.  I went in and changed those settings, but every time I close the workbook, it re-checks the "update links to other documents" and I have the same issue.  It changes the reference to the data tabs to add the name of the old file, as described in this thread.

This behavior is very frustrating!  Come on Microsoft!

At least now I understand the root of my problem. I have a problematic spreadsheet that is faulting and closing. When Excel restarts and recovers the spreadsheet all of the links to all of the pivot tables have been changed to hard links.

This is because Excel recovers the file with suffix like [recovered] or [version 2] and the file is in a data cache in the AppData folder. Then when I do a "save as" to save the recovered file back to the original file name and location all pivots get hard links. I have 30 some odd pivots that I have to go and update the links two or three times a day when the sheet crashes. Very frustrating.

I cannot add to data model because I need downward compatibility.

As of this date still no fix from Microsoft.

I have exactly the same problem and i have spent already 2 working days to solve this.

the story is more/less same as above.. well described really. 

I have been using the same (100% same) methodology to create reports. I have a standard way. never changed anything (relly anything) since June 2013. I always use .xlsb (binary) since the data is a bit large. (around 6-7 mb as .xlsb and if i use .xlsx it becomes 20mbs. )

I used to update the contents of the workbook, maybe hundreds of time in a day, i have a pivot at a 2nd workbook, i refresh it very often. that's it. then in the evening, i save the file. next morning, without opening, i make a copy via windows explorer. then i open the new file and continue the same. Last week i ve begun having this absolute link issue. 

so the only difference i made is, on another file, at home in the evening, i have been trying to understand "new" excel's "data model" features. i dont know what i have activated, during these tests. but it happened now and i cant roll back. 

i have 8 data WorkBooks and 8 PivotTables connected. All have been working fine until next week for verrryyyy long time. 

-Save As... doesnt work.

-i tried all above solutions, including the vba code, it can be a temporary solution maybe, but still not efficient.

Now looking forward to microsoft's solution/recommendation asap.

thanks for all the people above, it s a great feeling to see such a contribution. 

Regards,

Have you tried setting your hyperlink base address to C:?

FILE->Info (Show all Properties)

This should create hyperlinks with the UNC path.  Not sure how it works with linked data.

it is not a solution. 
the interesting thing is that, it doesnt happen everytime.

I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

Thus, we can easily say that this is a BUG. 

• Edited by HakanSD a few seconds ago

it is not a solution. 
the interesting thing is that, it doesnt happen everytime.

I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

Thus we can easily sayt that this is a BUG.

Also there are other complex formula references to other tables/sheets. Why these are not affected of the BUG but only the pivot source link?? 

• Edited by HakanSD 23 hours 56 minutes ago

it is not a solution. 
the interesting thing is that, it doesnt happen everytime.

I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

Thus we can easily sayt that this is a BUG.

Also there are other complex formula references to other tables/sheets. Why these are not affected of the BUG but only the pivot source link?? 

• Edited by HakanSD Saturday, April 11, 2015 7:30 AM

it is not a solution. 
the interesting thing is that, it doesnt happen everytime.

I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

Thus we can easily sayt that this is a BUG.

Also there are other complex formula references to other tables/sheets. Why these are not affected of the BUG but only the pivot source link?? 

• Edited by HakanSD Saturday, April 11, 2015 7:30 AM

it is not a solution. 
the interesting thing is that, it doesnt happen everytime.

I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

Thus we can easily sayt that this is a BUG.

Also there are other complex formula references to other tables/sheets. Why these are not affected of the BUG but only the pivot source link?? 

• Edited by HakanSD Saturday, April 11, 2015 7:30 AM

it is not a solution. 
the interesting thing is that, it doesnt happen everytime.

I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

Thus we can easily sayt that this is a BUG.

Also there are other complex formula references to other tables/sheets. Why these are not affected of the BUG but only the pivot source link?? 

• Edited by HakanSD Saturday, April 11, 2015 7:30 AM

it is not a solution. 
the interesting thing is that, it doesnt happen everytime.

I keep manually revising the data source muttering each morning, but sometimes i see the correct link (still as an absolute reference) with the new file name already set automatically. And the other pivot table in the same workbook cannot understand that the filename has changed and the absolute link to the old filename stays there. 

and the problem is, there is no stability. sometimes the other pivot is aware of the change and the first one does not. 

Thus we can easily sayt that this is a BUG.

Also there are other complex formula references to other tables/sheets. Why these are not affected of the BUG but only the pivot source link?? 

• Edited by HakanSD Saturday, April 11, 2015 7:30 AM

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

have just found this thread having discovered the same problem - agree entirely with the above!!!

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

have just found this thread having discovered the same problem - agree entirely with the above!!!

• Proposed as answer by nosp 11 hours 20 minutes ago
• Unproposed as answer by nosp 11 hours 20 minutes ago

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

have just found this thread having discovered the same problem - agree entirely with the above!!!

• Proposed as answer by nosp Sunday, May 31, 2015 8:08 PM
• Unproposed as answer by nosp Sunday, May 31, 2015 8:08 PM

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

have just found this thread having discovered the same problem - agree entirely with the above!!!

• Proposed as answer by nosp Sunday, May 31, 2015 8:08 PM
• Unproposed as answer by nosp Sunday, May 31, 2015 8:08 PM

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

have just found this thread having discovered the same problem - agree entirely with the above!!!

• Proposed as answer by nosp Sunday, May 31, 2015 8:08 PM
• Unproposed as answer by nosp Sunday, May 31, 2015 8:08 PM

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

have just found this thread having discovered the same problem - agree entirely with the above!!!

• Proposed as answer by nosp Sunday, May 31, 2015 8:08 PM
• Unproposed as answer by nosp Sunday, May 31, 2015 8:08 PM

this thread is open since Monday, August 19, 2013 3:29 PM and still there is no solution offered !!

have just found this thread having discovered the same problem - agree entirely with the above!!!

• Proposed as answer by nosp Sunday, May 31, 2015 8:08 PM
• Unproposed as answer by nosp Sunday, May 31, 2015 8:08 PM

• Proposed as answer by nosp 10 hours 58 minutes ago
• Edited by nosp 10 hours 54 minutes ago

• Proposed as answer by nosp Sunday, May 31, 2015 8:29 PM
• Edited by nosp Sunday, May 31, 2015 8:34 PM

• Proposed as answer by nosp Sunday, May 31, 2015 8:29 PM
• Edited by nosp Sunday, May 31, 2015 8:34 PM

• Proposed as answer by nosp Sunday, May 31, 2015 8:29 PM
• Edited by nosp Sunday, May 31, 2015 8:34 PM

• Proposed as answer by nosp Sunday, May 31, 2015 8:29 PM
• Edited by nosp Sunday, May 31, 2015 8:34 PM

• Proposed as answer by nosp Sunday, May 31, 2015 8:29 PM
• Edited by nosp Sunday, May 31, 2015 8:34 PM

I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

here is what I found and it works.

Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

 Select the corresponding "Table" or "Range" for your data source.

Continue with ALL your Charts, before do anything else.

After you finished by doing this will all your charts,

Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

Unselect "Save source data with File"

Select "Refresh data when opening the file"

Then will appear the following 2 alert message

1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

2. "Data will not be saved with other PivoTable report using the same source data as this report"

Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

It works for me!

Good Luck

• Edited by antonio_ags 4 hours 39 minutes ago

I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

here is what I found and it works.

Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

 Select the corresponding "Table" or "Range" for your data source.

Continue with ALL your Charts, before do anything else.

After you finished by doing this will all your charts,

Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

Unselect "Save source data with File"

Select "Refresh data when opening the file"

Then will appear the following 2 alert message

1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

2. "Data will not be saved with other PivoTable report using the same source data as this report"

Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

It works for me!

Good Luck

• Edited by antonio_ags Tuesday, June 09, 2015 2:49 AM

I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

here is what I found and it works.

Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

 Select the corresponding "Table" or "Range" for your data source.

Continue with ALL your Charts, before do anything else.

After you finished by doing this will all your charts,

Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

Unselect "Save source data with File"

Select "Refresh data when opening the file"

Then will appear the following 2 alert message

1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

2. "Data will not be saved with other PivoTable report using the same source data as this report"

Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

It works for me!

Good Luck

• Edited by antonio_ags Tuesday, June 09, 2015 2:49 AM

I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

here is what I found and it works.

Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

 Select the corresponding "Table" or "Range" for your data source.

Continue with ALL your Charts, before do anything else.

After you finished by doing this will all your charts,

Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

Unselect "Save source data with File"

Select "Refresh data when opening the file"

Then will appear the following 2 alert message

1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

2. "Data will not be saved with other PivoTable report using the same source data as this report"

Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

It works for me!

Good Luck

• Edited by antonio_ags Tuesday, June 09, 2015 2:49 AM

I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

here is what I found and it works.

Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

 Select the corresponding "Table" or "Range" for your data source.

Continue with ALL your Charts, before do anything else.

After you finished by doing this will all your charts,

Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

Unselect "Save source data with File"

Select "Refresh data when opening the file"

Then will appear the following 2 alert message

1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

2. "Data will not be saved with other PivoTable report using the same source data as this report"

Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

It works for me!

Good Luck

• Edited by antonio_ags Tuesday, June 09, 2015 2:49 AM

I think I found a good solution, when you have the data source in the same file, I did not tried if you have data source linked to anther files.

here is what I found and it works.

Open your file go to your Charts, select a chart, select ANALYZE, then "Change Data Source"

 Select the corresponding "Table" or "Range" for your data source.

Continue with ALL your Charts, before do anything else.

After you finished by doing this will all your charts,

Then, select any Chart, go to ANALYZE again, then in the left side select chart "Options" - Data

Unselect "Save source data with File"

Select "Refresh data when opening the file"

Then will appear the following 2 alert message

1. "Changes to the refresh on open option also change the option for other pivottable report based on the same source data. From now on, whenever you open this workbook, data will also be refreshed in the other report."

2. "Data will not be saved with other PivoTable report using the same source data as this report"

Then save your file, and open it again, try moving to a different place, or changing the name, and the data source will come from the originally tables or ranges has been chosen.

Important even when you start checking the data sources of the tables, do it again copy and paste or select again the tables or ranges,for all the charts then you see the 2 alerts messages, that's they key to solve this issue.

It works for me!

Good Luck

• Edited by antonio_ags Tuesday, June 09, 2015 2:49 AM

I also have the same problem:  Saving or Resaving files with Pivot Table connections within the same workbook convert to absolute and break the independent spreadsheet usability and portability and backups.

I tried disabling just save data with pivot table, and that didnt work. 

The second option you note to refresh every time it is opened, may not be viable for me as my sheet executes a rather large query...

Microsoft should allow pivot tables to use relative references as the permanent state of the the pivot table source reference for pivot tables. 

• Proposed as answer by ggoodfellow 8 hours 11 minutes ago

• Proposed as answer by ggoodfellow Monday, July 06, 2015 11:16 PM

• Proposed as answer by ggoodfellow Monday, July 06, 2015 11:16 PM

Hi All,

I appreciate the many posts and advice on this.  I too have been having this issue when I updated to 2013.  I tried all the remedies except VBA, and they did not work for me.

I had posted on another forum:

"Excel 2013 keeps updating the pivot table data ranges to a prior named file.

I built out a spreadsheet with multiple tabs of pivot tables all referencing the master data tab in the same spreadsheet.

When I "save as" the document as a new name, the pivot tables refer back to the old document.  Not sure why this is happening and it is very frustrating and time consuming to update the data ranges each time I save the document with an update. I am just changing the file name with V2, V3, V4 etc or changing the file name to have a different date extension (20150522 to 20150529)"

https://social.technet.microsoft.com/Forums/en-US/0cedfc28-aeba-469c-9a91-0389fc3c6937/excel-2013-not-updating-pivot-table-data-ranges-correctly-please-advise-how-to-fix?forum=excel

"Excel 2013 not updating Pivot Table data ranges correctly.  Please advise how to fix?

I was directed to this Forum.  The one thing I did find for NEW Excel 2013 files is the following:

However mine is was originally built in 2010, so it seems the only solution to completely rebuild the file in 2013.

It is surprising that this bug in logic has not been addressed yet and fixed.

Thank you so much for proposing this option. I have also been frustrated by this problem and have run down the solutions posted by everyone on this list. Unfortunately, none of them really worked reliable. However, your solution did seem to do the trick for me.  I highlighted the data in my worksheet that is the source for my pivot table, and I chose Insert > Table to turn my source data into a data table.  Then I saved my document with a new name.

Now each time I open the document, the error is gone, and the "old" filename is no longer associated with the data source of my pivot tables.

Thanks so much!

Hello, it's been almost 2 years and I don't see any answer... I have the same problem, since I change to Excel 2013, in other versions had no problem.

Does some body knows if SP1 fixes the problem?

Best regards,

Eugenio

I just try converting the data source into a table, as you indicate, however, Excel keeps adding the filename; I did not have to rename the file y just closed and opened again and the data source changed to what I said.

Thanks anyway!!

:)

I was having this issue too.

From what I've found, the best way is to create a new Excel file and copy the data in from the old file. You then create new PivotTables which will be linked to the file they're in when you create new versions, rather than the file they were originally linked to.

Take care though - when I create a new Excel file through 'right click, new, Microsoft Excel Worksheet' this will NOT work - you need to create the new file by opening Excel!

Best Regards

Robert

I have the same issue and I guess the reason caused this issue is very similar to yours. 

I have two Excel files with complicated models inside. One night I opened one of them at home using Excel 2016 for Mac, but not the other file. 

Then when I got back to work in office, I found the issue with the one that I opened at home while the other file without any issue. 

I guess opening the same file and sync through Onedrive may cause this issue for some reasons. 

Currently my workaround is to move (not copy) all the tabs from the original file to a new file. This seems solve the issue. But since my file includes some slicers, I need to disconnect them, update the data source, and then reconnect them. 

• Edited by LittleSun Monday, August 10, 2015 7:46 PM

I have the same issue and I guess the reason caused this issue is very similar to yours. 

I have two Excel files with complicated models inside. One night I opened one of them at home using Excel 2016 for Mac, but not the other file. 

Then when I got back to work in office, I found the issue with the one that I opened at home while the other file without any issue. 

I guess opening the same file and sync through Onedrive may cause this issue for some reasons. 

Currently my workaround is to move (not copy) all the tabs from the original file to a new file. This seems solve the issue. But since my file includes some slicers, I need to disconnect them, update the data source, and then reconnect them. 

• Edited by LittleSun 11 hours 42 minutes ago

Dear all,

I figured out in Excel 2013 the settings in the section Info might solve the issue

If you are having this problem and use Named Ranges, here is a potential workaround: Scope the names to the sheet instead of the workbook. 

I.e., Formulas / Define Name / Scope = [Sheet Name].

The root problem is, Excel is inserting the file name in the pivot table data source, inappropriately.  The data source may appear to be clean when initially working with the pivot table, but when you close the workbook and reopen it, the file name will always have been reinserted.  For example, "MyRange" (named range), after reopening will be "'New Microsoft Excel Worksheet.xlsx'!MyRange".

Excel 13 seems to want to do this when you set the pivot table data source to a range that does not require a bang (!).

So workbook-scoped names (which I have used with pivot tables for years) will trigger the bad Excel behavior, because "!" is not needed to reference the named range.  Unnamed ranges (e.g., "A1:C5") within the same worksheet may work similarly, but I haven't tested those.

Sheet-scoped names defining a range on a different sheet will require the sheet name before the name (e.g., Sheet1!Range1).  When that sheet reference (!) is there in the pivot table data source, Excel seems satisfied and doesn't mess with it anymore.

This is clearly a bug and should be fixed by Microsoft.

p.s. Tried all of the solutions posted in this thread to date (except VB), none worked for me.  The problem doesn't manifest in all my Excel files.  The files it's happening in were all created in Excel 2013.  Others were possibly created in Excel 2010.

• Edited by Edith Wharton 14 hours 59 minutes ago

The workaround worked for a few file copies Excel got creative and found a way to work the file name in anyway.  It is now inserting the file name in this format:

'[New Microsoft Excel Worksheet.xlsx]Sheet1'!MyRange

I give up.