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...
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