I have a .XLSX file, that was created long time ago (I don\'t even know in which

Question

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: 'UsersSergii_LitnevskyiDesktopNew 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

Anyone ?

Explanation / Answer

I have the same issue and this is with one Pivot Table that pulls data from another sheet in the same file.

The sheet that contains the source data pulls from a SQL server, not sure if this is relevant or not.

If I move the file to another folder or send it to a client the PivotTable keeps looking for the old file which doesn't exist.

The only workaround I was able to find was to click the tick-box "Add this data to the Data Model" when creating the Pivot Table.

Then the hard-coded reference to a fixed file name issue seems to disappear.

It really looks like bug to me, why would the Pivot Table need to store a link to a hard-coded file name if the source for the Pivot Table is in the same file?

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.