This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
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.
Hello Frances,
Since you need help with creating VBA code/Macro enabled file, you may post your query in
customization forum:
http://answers.microsoft.com/en-us/office/forum/customize
Thank you.
For crying out loud. Does anyone from Microsoft evergive a straight answer to a straight-forward question? Please read the question, stop directing people to other threads that have no relevance, and offer an answer.
I am also having this issue with data source references changing in pivot tables (Excel 2010) and would like to have it so that my master file that contains the pivot tables have and retain a data source reference that does not change unless I change it.
Microsoft: Is this or is this not possible?
Thank-you.
True! we are not asking for a customization but we are looking for a workaround of what looks like a serious issue for Excel practitioners.
Once an excel file is created with the absolute path for PivotTableDataSource, it looks like it is damned forever, no matter what you change in advanced options. It is still not clear to me what triggers the absolute path, it looks like it happens only in excel files where I’ve lots of pivot.
However, if you go in Excel Options/Advanced Options/Save external link values and uncheck it, then you should be able to modify all PivotTableDataSource from absolute to relative. I’ve written some code to do this and apparently the excel files are ok after that, but I’ve not tested it extensively.
True! we are not asking for a customization but we are looking for a workaround of what looks like a serious issue for Excel practitioners.
Once an excel file is created with the absolute path for PivotTableDataSource, it looks like it is damned forever, no matter what you change in advanced options. It is still not clear to me what triggers the absolute path, it looks like it happens only in excel files where I’ve lots of pivot.
However, if you go in Excel Options/Advanced Options/Save external link values and uncheck it, then you should be able to modify all PivotTableDataSource from absolute to relative. I’ve written some code to do this and apparently the excel files are ok after that, but I’ve not tested it extensively.
The work around that fixed this issue for me was to create a data model and use the data model as the data source for the pivot table.
The data model is a feature of the power pivot add-in which is available for free on Microsoft's website.
Hope this helps.
For crying out loud. Does anyone from Microsoft evergive a straight answer to a straight-forward question? Please read the question, stop directing people to other threads that have no relevance, and offer an answer.
I am also having this issue with data source references changing in pivot tables (Excel 2010) and would like to have it so that my master file that contains the pivot tables have and retain a data source reference that does not change unless I change it.
Microsoft: Is this or is this not possible?
Thank-you.
It was super frustrating for me too. I tried multiple different things till one solution clicked for me.
The work around that fixed this issue for me was to create a data model and use the data model as the data source for the pivot table.
The data model is a feature of the power pivot add-in which is available for free on Microsoft's website.
Hope this helps.