Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Links to external source are not preserved

0

HI!

One of my reports is linked to a pivot table in a different file. The idea is that once I update the pivot table the report will be updated automatically. I've been doing it like this for years.

All of a sudden, Excel stopped preserving the links - the path to the source file is removed and replaced with REF#.

Looks like this:

GETPIVOTDATA("Sum of GrossPay",#REF!,"Depot Name","Roseburg","Dptmt","Distribution")

I checked the Trust Center settings and made sure that the file server is added as trusted location with all subfolders included. The source file is open when I open the report.  

Also, multiple people are able to edit both files.

Please HELP!

Marina

Answer
Discuss

Discussion

I updated my answer; see if the new solution works (it seems like it should).
don (rep: 1989) Jul 12, '16 at 9:36 pm
I did this too :-) and it would solve my problem if my files weren't so complex. I am using it as a temporary work-around.
I did not have any issues until a week or so ago, so I think that there was a change in some background settings...
Marina Jul 12, '16 at 10:10 pm
It sholdn't matter how complex the files are since you are just moving a formula from one workbook to the other workbook. How does that solution not solve the issue? The only new thing is that you then have to have an extra worksheet in the source workbook.

What I would do is create a separate worksheet for all the formulas and then lock that worksheet and reference the cells on that sheet. If you have lots of formulas and managing all of them would be the complex task then simply mirror the worksheets that currently have the formulas, as in, copy everything, or at least the desired formulas, onto the same cells in an empty worksheet on the other workbook. Give that worksheet the same name as the one that used to have the formulas; it will be easy to reference the data and you just have to remember to go to the source data to update any formulas.

There are no background settings that I can think of except for if you set the calculation options to anything other than Automatic.
don (rep: 1989) Jul 12, '16 at 10:55 pm
Without direct access to your system, it is difficult to pinpoint the problem, which I suspect involves a human. As such, it seems likely that this problem might persist into the future, regardless of your attempts to prevent it. That said, I believe that what you consider to be the workaround is probably the best solution in the long-run.
don (rep: 1989) Jul 12, '16 at 10:57 pm
i will do that, thanks! mirroring my report is definitely a better idea than mirroring a pivot table. I don't know why i didn't think of that. Thank you! :-)
Marina Jul 12, '16 at 10:59 pm
I will update my answer below and make sure to select it as the Answer so people who come here in the future know that that solved the issue.
don (rep: 1989) Jul 13, '16 at 9:21 pm
Add to Discussion

Answers

0

It seems to me like someone has changed some links and re-saved the document to which you are trying to link.

It's a dangerous situation when multiple people are able to edit documents on a shared server and there are formula links between the two files.

What I would do is to reset the links and make sure they work and then lock the cells that have the links and also those to which you are linking. Make it so that can't be changed.

If you need the user to be able to change source data, then let them do that but use another cell within the same workbook to reference that source data and then link to the reference cells and not directly to the source data. This method can also help to preserve links.

Update:

Try this:

Place the GETPIVOTDATA function in the same file as the PivotTable (in a blank area of the same sheet, or in another sheet), and then link the destination file to the appropriate cell [the cell that has the GETPIVOTDATA function in it].

I got that from this link on the Microsoft website: GETPIVOTDATA Issue

See if that works. The link says it is for an older version of Excel but give it a try anyway.

Update 2:

In addition to putting the pivot formulas in the worksheet that contains the pivot table, you can manage all of the formulas and functions that you want to appear in the linked workbook by mirroring those reports in the source workbook that contains the pivottable.

Simply mirror the worksheets that currently have the formulas, as in, copy everything, or at least the desired formulas, onto the same cells in an empty worksheet in the workbook that has the pivottable. Give that new worksheet the same name as the one that used to have the formulas; it will be easy to reference the data and you just have to remember to go to the source data workbook (the one with the pivottable) to update any formulas.

Discuss

Discussion

Thank you for your attention to my problem! but I ruled it out. At this time it was just me using both files and the path to the file was removed. I have to mention that it only happens to links to pivot tables. Regular links to cells are not affected. 
Marina Jul 12, '16 at 9:12 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login