Data Source path in Pivot Table changes to absolute on its own

Anonymous
2013-08-19T15:32:36+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

45 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2016-02-13T20:26:58+00:00

    Problem is solved. Go to link 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

    Looked at the link, but there are only suggestions that work for some and don't work for other. I don't think a VBA macro to remove the fixed references is a fix.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-02-01T17:50:49+00:00

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-01-27T21:49:25+00:00

    CLW

    "when I do a "Refresh All" it still seems to want to open the old version of the file"

    If you open the "Change Pivot Table Data Source" dialogue, you will see that the original table selection now has a  "[Original Spreadsheet]" reference added to the source reference. If you share this file on the network with someone else, the same relative to fixed reference change may also take place.

    You can avoid this problem by going back to Excel 2010, but that's less than desirable.

    On a side note:

    I've upgraded to Windows 10 since I had this problem July last year. I've tried a couple of time to recreate the problem on a new spreadsheet in 2013 and haven't had the problem show up so I have a question to the group:

     > Anyone having this problem on Window 10 or is it a Windows 7 and earlier problem?

    I'll do a little more checking, but I am wondering if this problem is operating system related.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-01-27T16:37:13+00:00

    Hi All

    I am very new to Excel 2013 and still have a lot to learn as this is a new job function I only started October 2015.  I have to use this application for my daily functions.  I am currently doing a dashboard with PowerPivot and Slicers.  I used a bit of Excel 2007 some years ago.  I love the new features in 2013.  I find it is so much easier.  I have not even begun to use the VBA coding and I have been able to do the required reporting.

    However, I have had to make some changes but keep the old version of the file.  In the new version of the file when I do a "Refresh All" it still seems to want to open the old version of the file.  However, I have checked the connections and my data model has the correct last refresh date and time.

    As everyone else I would like to get a resolution to this.  I have used bing and did some searches and read some threads but I have not been able to find a resolution that works.  The message can be annoying and each time that I update a field in the data model I have to de-select and re-select the field in the pivot table for it to accept the changes.

    Thanks and regards

    CLW

    Was this answer helpful?

    0 comments
  5. Anonymous
    2016-01-26T09:27:27+00:00

    You are right. If you are just using 2010 the problem is not showing up.

    At least I was not able to reproduce the problem within 20 name changes of the file.

    Nevertheless it will hit the customer again, when they are migrating to 2013 or 2016. :( Rebuilding all the legacy files with data model approach is not a real option.

    Was this answer helpful?

    0 comments