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
    2014-04-07T17:59:40+00:00

    I'm having this exact same problem, and I can't solve it.

    I think right now the only way is to use a macro do manually force the right source relative path each time the workbook is opened. And thats terrible.

    Is there a fix for that, MS??????????

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-09-08T11:04:56+00:00

    Hi Serg,

    For better suggestions related Pivot table in Excel, you may post your query in Excel IT pro using the forum link below:

    http://social.technet.microsoft.com/Forums/en/excel/threads

    Thank you.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-08-22T09:48:23+00:00

    So, I guess there is no fix for this issue???

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Ashish Mathur 102.4K Reputation points Volunteer Moderator
    2013-08-19T23:25:35+00:00

    Hi,

    Even though you have mentioned that "All of the Pivot Tables use other sheets from the same file as Data Source", it looks like some Pivot Tables are coming from an external MS Excel file.  That's the only way (I think), you can have the entire path display.

    Was this answer helpful?

    0 comments