Data Source path in Pivot Table changes to absolute on its own

Anonymous

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

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

Anonymous

2014-05-30T18:09:32+00:00

Thank-you! I will give that a try.
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-10T22:55:47+00:00

For those of you who are experiencing this problem - did you right click in Windows and create a new Excel file that way? There is a bug where this occurs if the file was originally created through Windows Explorer.

Anita
Was this answer helpful?

3 people found this answer helpful.

0 comments No comments
Anonymous

2014-09-11T12:33:53+00:00

Hello Anita

in case it was created through Windows Explorer (so it is bugged from the birth), is there a suggested approach to recover the work without rebuilding the excel from scratch? moving the pivots and the data to a different workbook could help? I'll give it a try, just in case you already have a work-around

thanks

francesco
Was this answer helpful?

0 comments No comments
Anonymous

2014-09-11T15:28:15+00:00

Hi Francesco,

With a little more research, we have a root cause. When you right-click to create the file, the option for "Remove personal information from file properties" is selected in Trust Center Privacy settings. I've tested removing that option before creating the PivotTable, and that stops the issue from happening. So that'd be your workaround.

Regards,

Anita
Was this answer helpful?

3 people found this answer helpful.

0 comments No comments
Anonymous

2014-12-10T18:09:15+00:00

I found a work around - File - Options - Document Inpector - Inspect document and you will see that it finds the absolute path reference and you can click a button to remove the absolute path and it works from then on.

Note that the tick box "add to data model" is not a good work around because you can't add calculated fields to you pivot table when you do this.

Microsoft should please find a solution this was many hours of debugging for simple problem.
Was this answer helpful?

4 people found this answer helpful.

0 comments No comments