I use pivot tables to track production goals.  I archive the pipeline monthly and when I do the pivot table data source stays linked to the master pipeline. 

Example '[Master Pipeline Report.xlsx]DATA'!$A$1:$BX$839

It should be 'DATA'!$A$1:$BX$839

It causes all kinds of problems and the archive is never accurate.  I decided to move the pivot tables and report to a separate workbook.  I deleted the "DATA" tab in my new workbook because the pivot tables should look at the Master Pipeline Report DATA tab.  When I did a refresh I received errors and when I check each of the pivot tables there are 4 out of 20 that linked to the new workbook.

Example 'M:\M\Pipeline Report\Scorecard\[Scorecard 05.28.15.xlsx]DATA'!$A$1:$BX$839

All of the pivot tables were created by the same person on the same day.  I don't understand why they are looking at different data sources when a file save as is done.

Hi T.Hurley,

Based on your description, you linked your PivotTable data source to the master pipieline. Could you tell me what is master pipieline? Is this a third-party program?

I suppose if your issue might be caused by relative path? When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available.

There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.

Scenario 1:

1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
2. You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
3. You open the file by a UNC path.
4. As a consequence the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. In other words, the Share name is eliminated from the path.

Scenario 2:

1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
2. You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.
3. As a consequence, the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1.

Please refer to this article to get more information.

https://support.microsoft.com/en-us/kb/328440/en-us

Hope its helpful.

Regards,

George Zhao
TechNet Community Support

Thanks for your reply.

The pipeline is a workbook with several worksheets which included the pivot tables.  The pivot tables are linked to the data worksheet within the same workbook.  When I file save as to archive the pipeline workbook at the end of each month the pivot tables don't recognize the new workbook name.  They direct back to the master pipeline workbook which is an ever changing workbook.  I need the archived pipeline pivot tables to use the data tab in the archived pipeline as the data source not the master pipeline that changes daily.

Hi T.Hurley,

Did you use Excel 2013? As far as I know this is a known issue in excel. For this issue there is a workaround you can try is to save the file as *.xls file.  Please refer to this thread below:

https://social.technet.microsoft.com/Forums/office/en-US/43bf5110-dfad-40e5-a71c-e9736da6fbc2/data-source-path-in-pivot-table-changes-to-absolute-on-its-own?forum=excel

Hope its helpful.

Regards,