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-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
  2. 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
  3. 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
  4. Anonymous
    2015-04-21T14:16:31+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

    Was this answer helpful?

    0 comments
  5. Anonymous
    2015-02-05T17:14:26+00:00

    It is very annoying to have these kind of problems due to "intelligent behaviour" of Excel

    Please MS solve the issue.

    By the way, some files of mine work fine and others don't. 

    I even have a file where one pivot changes and another doesn´t

    Was this answer helpful?

    0 comments