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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2015-07-30T19:41:53+00:00

    I also agree...this is a pain the you know what...it did not always work this way...definitely a bug...I am going to try to model suggestion for now

    I've done some more testing of different situations.

    What I found is it only changes from relative to absolute under some circumstances. It may be related to enabling macro's, but doesn't seem to be a problem with the same spreadsheets when I open them with relative references in Excel 2010 and then save them to a different name.

    I had a Microsoft support chat remote into my machine, and when he did that I couldn't replicate the problem by saving a spreadsheet to a different file name. However as soon as the remote login was disconnected, the problem occurred again.

    Not sure how to demonstrate this to Microsoft so that they can troubleshoot it, but I'm going to use Excel 2010 until they do and check that it doesn't change references to absolute.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-09-15T20:44:07+00:00

    This is not support!  You link to the forum doesn't even search for the subject of this forum thread.  Completely annoying!

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2016-01-21T11:11:03+00:00

    I'm also very sure, that it is a bug in Excel 2010 and Excel 2013. Haven't checked Excel 2016 yet. But there is a certain chance, that those old bits are still in the current version.

    This is a bug, which can render a management report completely useless at the manager's desk. Just by renaming the Excel file. This is a bad customer experience on the level of decision makers.

    I also tried to get a solution via different channels or at least to get the impression that Microsoft is listening and take over the chance to remove a bug in the product.

    Dear Microsoft, what is the best way to tackle this challenge?

    By the way, I would be happy to give somebody at Microsoft the opportunity to reproduce the bug.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-01-21T16:34:29+00:00

    frankbrew

    I've reverted to using Excel 2010 and haven't seen the problems saving and renaming the file on the same computer.

    I version control spreadsheets as I work on them so the name changes frequently and I've checked to see if the references change to static reference and not the pivot tables in the original sheet. 2010 doesn't seem to have a problem in this use case, but I'm not sure about network drives or other changes to my use case. I seem to remember some reference to the problem happening on network drives when a different user opens the file.

    Can you replicate the problem in 2010? If you can what's the use case and I can give it a try.

    As I mentioned almost a year ago - The problem couldn't be replicated when the computer was remoted into by Microsoft support but returned as soon as they logged out.

    You could also try calling Microsoft support and get them to remote in to demonstrate the problem. At least we would know if remoting in stops the problem.

    You may also be able to write some VBA to convert remove the fixed reference portion of the pivot table when it is opened. That was my next option if rolling back to 2010 didn't work for me.

    I feel you pain.

    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