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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-02-01T17:50:49+00:00

    Was this answer helpful?

    0 comments
  2. 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
  3. 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
  4. 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
  5. Anonymous
    2016-01-21T17:04:30+00:00

    Hi,

    just to contribute to this very painful issue.

    I've upgraded to Excel 2016 and the problem with absolute path in pivot tables after renaming the file (or saving it in a different folder as well) persists as it was in Excel 2013.

    At least, with Excel 2016 I'm experiencing much better performance than 2013 when dealing with workbooks containing thousands of sheets (+2000 was more or less the treshold, it got stuck in some strange loop, even if I was running Windows 8.1 64bit with 16GB RAM). 

    Personally, with Excel 2013 I had to install also Excel 2010 (mostly due to the issue on performance above), while I had to write some code to remove absolute path from pivot and I'm still using the same code.

    I find it hard to believe that in Microsoft they are not able to replicate the problem. 

    Overall, i find Office 2016 much much better than 2010, so reverting is a tough choice.

    I have not experimented to see if the issue of absolute path is only related to spreadsheet generated in 2013 or if it emerges also with workbook created from scratch with 2016.

    regards

    Francesco

    Was this answer helpful?

    0 comments