I'm working with some test files, trying to figure out how I can have excel workbooks existing only on Sharepoint (not stored locally on a PC) and yet connect some columns between them using either formulas, Data Connections - or any other means.
I've looked through lots of sites and forum posts, and this one especially: https://support.office.com/en-us/art...8-744766ccdc94 but the solution is still escaping me. On this particular page it states (in numerous places) "Important: Depending on how your particular environment is configured, you might or might not be able to refresh the data in a workbook that you are viewing in a browser window. For more information, contact a SharePoint administrator.". I don't have a sharepoint administrator to discuss this with, this is using a Sharepoint provided by Microsoft with my MS Exchange account that I pay monthly for.
I've tried both a formulaic as well as a Data Connection approach; I prefer the Data Connection approach so that the end user doesn't have to open both files to get updated data (one user will be updating the source workbook (Book1) and another user will be opening Book2 that should update based on latest data saved in Book1. When I use formulas, it requires Book1 to be open for the formulas to read it.
One catch was that my pc login/Office 365 account is personal and doesn't come with Sharepoint; My exchange email account DOES come with Sharepoint, but I can't actually log into Windows with it so the permissions were a factor. I was able to dive down into the permissions of the connection and tell it to use my Exchange account, and that worked opening the target file (Book2) on the PC, pointed back to the source (Book1) on Sharepoint. However, I cannot figure out any way to make this work with BOTH files on Sharepoint.
When I attempt to put Book2 on Sharepoint, open it and get it to update via the connection I get the errors attached.
I know I'm going to regret saying this ... but surely Microsoft has some methodology to accomplish this, right??
Bookmarks