Problems with refreshing pivot tables from different computers

Question

We use excel pivot tables all the time for management reporting which are stored in dropbox so all the team can access them.  The problem comes when the pivot tables need refreshing.  If this is done from a computer that was not used orginally to set up the pivot table we get an error message - could not find part of the path.   https://www.screencast.com/t/URatNCvL3M7

This is a pain especially when someone is on holiday.

Does anyone know how to stop this happening?

Jay · Answer

Since this is more of how Excel works, there isn’t much I can advise on how to resolve this.

In general though, if the Excel file is located here, c:\users\jay\dropbox\finance\report.xlsx then that is the absolute link.

The pivot table could refer to a file located in c:\users\jay\dropbox\finance\booking.xlsx, again an absolute link.

To make it a relative link, the pivot table reference needs to change to just \booking.xlsx, or however Excel creates them.

As this is very Excel-specific, I can't be more helpful on this matter, though perhaps another user on the community can assist further!

KW6 · Answer

Thanks Jay for your reply.  At the moment we do option 1 but this breaks down if the computer is out of the office and the person unavailable for whatever reason.  Would like to do 2 but I am not very techy so I have tried to read the link you attached but it makes little sense to me. :dizzy_face:

If I give you a little more background would you be able to spell it out for me?  We have dropbox set up with some shared folders for our team; one of which is called reports.  There are about 5 reports which update overnight into the shared folder eg a booking report.  Everyone can access these.

The info in the reports is huge so I have built some pivot tables to summarise the management info we need ie a booking report pivot.  To update the pivot the data needs to be refreshed but if anyone else tries to refresh the data they get the error above saying the link to the booking report can't be found even though they have access to the booking report.

How do I edit the link to make it a relative one?  If I go to Data/Edit connections it is greyed out but I can access Queries and Connections though not sure what to do next or if I am in the right place.

If I click on Existing Connections nothing shows up - https://www.screencast.com/t/oSPTs8Of4phC

Jay · Answer

Hi @KW6, thanks for joining the Community!

This would be expected since the pivot table appears to be referring a specific document only available on one user’s machine.

Depending on how the link was made in Excel, it could be directly referring to a file and its entire folder path, which won’t be the same on another computer due to different usernames and folder structures.

There are two workarounds for this:

* Access and change the file directly on the computer it was created on with the correct path.
* Change the link in the Excel document to be a relative link, and not an absolute link, assuming all the files are in the same relative locations in Dropbox.

Hope this helps to clarify matters!