Excel file can't find source Excel file

Occasional Contributor


Not sure if I should post here or in the Excel Community.

I have two Excel workbooks in the same folder on a SharePoint resource. One is the data source. It is a large workbook with lots of raw data aggregated and cleaned up via Power Query. The other is a small report workbook showing end-of-month summaries.

I’m trying to populate a couple of report columns with a formula that reaches over to the data workbook. When I open the report workbook and click through the update boxes, the columns using the formula that calls the data workbook go to #REF!.

Going to Data on the ribbon and clicking Edit Links, I see that the source status is “Unknown”.




When I click “Check Status”, the status goes to “Error: Worksheet not found”. (I have not moved the file. They are still both in the same folder on a SharePoint resource.)



If I go through the process of “Change Source…” and point again to the source file location, it still won’t work.

However, if I open the source file, so both source and destination files are open, then everything snaps together and the formulas in the report update and populate the summary report.

That seems to be a work-around, tell my users that they need to open both the data source file and the report file at the same time. Seems an inelegant solution. (My users asked me to split the monthly summaries from the raw data because the file was too big and slow.)

Our organization migrated from NAS solutions to SharePoint around the first of the year, so SharePoint is new to me.

Any thoughts/suggestions?

1 Reply
best response confirmed by Brian_Paasch (Occasional Contributor)
Since nobody from MS answered this with "that's a feature, not a bug", I started to wonder if this problem might not be you (SharePoint), but is me. But "me" how/what? The only awful thought I could think of is that my source, data Excel file was corrupted. After a couple days of trying hard to ignore that possibility, I gave up and began the task of rebuilding the source file from scratch. Once that painful task was complete, I pointed the report file to the new source file, and.... magic! Everything now works. Based on that, I assume my original source Excel file was corrupted. Painful!