Forum Discussion
Referencing data in my formulas from specific file names in SharePoint.
We have a company SharePoint site with multiple subfolders each containing monthly excel spreadsheets. In one of the folders, I have my master spreadsheet that where the formulas are pulling in data from the files that are being uploaded. This works when I open my master spreadsheet and one of the monthly sheets, (e.g., East-February, or West-January and then link the two cells with an = and click on the source cell.
On my Master spreadsheet the formula looks something like this:
='[West-February.26.xlsb]MontlyBudget'!A1
There are about 17 different folders however and instead of opening each one and redoing this every time one in uploaded. I would rather just copy the formula on my master spreadsheet i.e.
='[East-February.26.xlsb]MontlyBudget'!A1
='[North-February.26.xlsb]MontlyBudget'!A1
='[South-February.26.xlsb]MontlyBudget'!A1
etc.
Ideally when the North area uploads their spreadsheet the formula just pulls in that data.
What is happening is that I am copying the exact formula over to my new cell, I end up with a #REF error message and cannot seem link.
2 questions:
- Can I link these without opening up each spreadsheet separately and copying and pasting the link each time?
- Can I link these to future files that aren't yet created, e.g., Once West-December.26xlsb is uploaded, the data automatically pulls in?
3 Replies
Hello HKRONIN,
The #REF! occurs because the external workbook reference cannot be resolved until the source workbook exists.
For 17 folders, I would avoid creating individual workbook links. Power Query is a better fit here. You can connect to the SharePoint folder, filter for the required files, and load the data into the master workbook.
When a new monthly file is uploaded, refresh the query and Power Query can pick it up, provided it follows the same structure.
So you don't need to open each workbook and create the links manually.
Data > Get Data > From SharePoint Folder
Question. If I save the files with the proper names and have them already linked. Will it break the links if someone saves a file with the same name over top of them?
For example. Right now it is only September. But what if I save files for October, November, and December with blank data, and at some point, someone saves a "December" file replacing my "December" file. Would that break the link?
I believe the answers are No and No.
You might want to consider making the master the data source and then pulling data from the master to the subsheets so you only have 1 data connection per sheet but you will still need both open for that data to get updated using a standard cell links like that.
Alternatively you should consider using power query (data tab and go to get data and select file and then select excel file) and then you can use a refresh data button to update the data and that should work without having to open the other sheet(s).
In no case will you be able to link to a file that isn't created yet. (except if you use some sort of VBA code in which case you could but note that it would have to run in the desktop version to work)
