Pivot Table connections to a SharePoint File

[Mark Bit]

Hey all, as always thanks for all your help. It is strange how some of the more "basic" issues are the ones I have the hardest times with.

I have a file that is currently not saved on my company SharePoint that extracts data via a Pivot Table that is on the SharePoint. I am not sure if both files need to be on SharePoint to work properly - so if that is it - then easy fix, I can just save both to SharePoint.

But the issue I am having is this: "We couldn't get the data from 'Master' in the workbook "XXX" Open the workbook in Excel and try again.

I am creating a "form" of sorts so that colleagues can enter variable data in some fields and see how a certain outcome would play out in the circumstance. For reference we are talking Retail promotions. How a promo of $x would change net profit. The variables interact with constants to create this finding. My challenge is to provide a dynamic form for the users, so it can be more efficient and less strenuous on their part. Currently, they have to put in all the data themselves - even the constants. I developed this form (with help from this site! ) that dynamically inputs the constants for the user - and dramatically saves time. But..... in order for the form to update with the most updated constants, it needs to access and refers the Pivot Table that fetches data from the SharePoint.

Question is: How can I have the Pivot fetch data from the SharePoint without me having to have the end user also open up that file? The strange thing is, I have other files that fetches data from SharePoint or other files just fine - without having to resort to opening the file. In each instance I never set any options or settings to do so. It really is hit or miss whether or not data is extracted via SharePoint flawlessly or not.

Any ideas? Is my issue worded correctly enough to draw any conclusions?

**I have added the screenshot of the error***


TL;DR: Getting an error to open a file sitting on SharePoint in order to refresh a Pivot on my base file. I am looking to sync data without resorting to opening another file.

 

[bobhc]

Put all the files that are used or needed when the users are carrying out the task, I think the error message is telling you that.
Where is the file that is not on SharePoint but is the front end for your users?

In each instance I never set any options or settings to do so.

they are there but accessed via the SharePoint settings.


.

 

[Mark Bit]

Put all the files that are used or needed when the users are carrying out the task, I think the error message is telling you that.
Where is the file that is not on SharePoint but is the front end for your users?


they are there but accessed via the SharePoint settings.

.

Are you saying that it is actually a SharePoint setting that is prompting the error?

And are you also saying both files should be on SharePoint to "connect" correctly without having to open both?

Currently, the file that will be worked on, that extracts data from SharePoint, could be on end-users Desktops or internal non SharePoint share drive.

 

[bobhc]

If the files are on users desktop then you have the worst case going.
User one is working on the file, but user two is near the end of inputting data and closing the file, all the data that two has entered is not available to one, as twos file is still open, so one is working with data that will shortly be updated, therefore his reading of the data on his desktop is wrong.
If files and data are all one SharePoint then all files will be updated live, as two inputs one sees the end result as per the output of the files.

.

 

[Mark Bit]

Oh I am sorry, I should have specified. Each End-User has their own copy of the template form. So at no point will two people be working on the same file or overwrite information. There will be around 20 separate and unique files, based on the template built. But it is the template that is extracting data stored on the SharePoint.

When connected to my work server, I am always connected to the SharePoint. They will be too, most often than not. I just dont want them to have to open a second file each time just to get the template to work.

For me it is just rather confusing, since I have other files that reference data - via pivots - stored on the SharePoint and do not require the file open.