Situation
You have a shared file repository containing useful information (for instance a RAID log) and you would like to be able to used some elements of that information in a calculation in another excel file. You can’t use the normal method (=[FileName.xlsx]SheetName!A1) to call information from a cell in another workbook because the source is held on the SharePoint.
Solution
If you follow the steps below you will be able to establish a link to any given cell in the source workbook. You can then either use this in any calculation (thought that does tend to make for a rather long calculation) or then reference this cell in any calculation.
- Bring up the SharePoint list with your source file in, right mouse on the file and chose “copy shortcut”
- Go to excel and select your target cell and Paste. This will paste the shortcut into Excel in your target cell.
https://teasmspace/sub/sub/sub/FileName.xlsx
- Enclose the file name in square brackets and add in the sheet name
https://teasmspace/sub/sub/sub/[FileName.xlsx]
- Add the sheet name and cell reference onto the end of the link using the normal format. If in doubt go to the source file and make a link to the desired cell from a different sheet; copy the formula that excel uses.
https://teasmspace/sub/sub/sub/[FileName.xlsx]SheetName!A1
- Put a =‘ at the start of the link. This will now display the source date.
=’https://teasmspace/sub/sub/sub/[FileName.xlsx]SheetName!A1
Notes and comments
One rather neat feature of this method is that the source workbook doesn’t need to be open to gather the data. This means that your sheet will always be using the latest saved version of the source workbook.
To force an update click on "edit links", select them all and press "update values".
If you need to bring across a number of cells, for instance you want to bring across a whole table of information to analyse, you need to make sure that the cell reference is shown as A1 rather than $A$1. This will then allow you to copy this formula any number of times as you copy across rows and columns Excel will map to corresponding cells in the source
If you have a number of files in the same SharePoint list with the same structure which you want to harvest information from you can very easy change the file name with a search/replace. Set up the first collection of data from FileNameA and then copy this block to another location and select it. Search/Replace FileNameA with FileNameB and Replace All (which will only work on the new block that you’ve selected) and you now have data from the second file.