Day 17 - Excel file linking
I’ve always said, Excel runs the world!? When you live your life in Excel, the ability to link spreadsheets together is an essential feature, regardless of where the file is stored.? Considering SharePoint Embedded supports collaborating on Office documents, linking to spreadsheets is as easy as knowing where its stored.
When to use this
Imagine you have a source spreadsheet that contains all your budget items.? This is a controlled spreadsheet that only a few folks will have permission to edit.? You want others to know about the budget, specifically what they are responsible for.? Others can easily incorporate the “master” budget numbers into their own spreadsheets by linking the two together.? Doing this allows the people controlling to the budget to update it, and those consuming the budget numbers through links can receive the most current numbers automatically.
Linking spreadsheets
Linking spreadsheets is simply a matter of where is the source spreadsheet and what cell do you want to reference.? When the spreadsheet is stored in SharePoint Embedded, you can easily get determine where the file is located in Excel itself.
Get the source spreadsheet location
NOTE:? Link refreshing is only supported when in Excel Desktop mode.
2. Navigate to File -> Info and click the “Copy path” button.? This will get the file location in the SharePoint Embedded container.? It should resemble something like this:
https://<domain>.sharepoint.com/contentstorage/CSP_XXX-41ad-88ad-ddffcfdb75fe/Document%20Library/StatusReports/Excel%20Master%20Spreadsheet.xlsx?web=1
?
领英推荐
?
Create the link reference in the consuming spreadsheet
Again, open the consuming spreadsheet (where you want to see the linked value) in desktop mode.
1.?????? Select the cell you want to view the linked value.
2.?????? Insert the source spreadsheet location to include the sheet and cell like you normally would.? It should resemble this:
When you open the consuming spreadsheet you can manage and refresh the links normally.
The full URL to the source spreadsheet should look like this:
='https://<Domain>.sharepoint.com/contentstorage/CSP_XX-28af-41ad-88ad-ddffcfdb75fe/Document Library/StatusReports/[Excel Master Spreadsheet.xlsx]Sheet1'!$C$2