SOLVED

Excel file can't find source Excel file

%3CLINGO-SUB%20id%3D%22lingo-sub-3272834%22%20slang%3D%22en-US%22%3EExcel%20file%20can't%20find%20source%20Excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3272834%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3ENot%20sure%20if%20I%20should%20post%20here%20or%20in%20the%20Excel%20Community.%3C%2FP%3E%3CP%3EI%20have%20two%20Excel%20workbooks%20in%20the%20same%20folder%20on%20a%20SharePoint%20resource.%20One%20is%20the%20data%20source.%20It%20is%20a%20large%20workbook%20with%20lots%20of%20raw%20data%20aggregated%20and%20cleaned%20up%20via%20Power%20Query.%20The%20other%20is%20a%20small%20report%20workbook%20showing%20end-of-month%20summaries.%3C%2FP%3E%3CP%3EI%E2%80%99m%20trying%20to%20populate%20a%20couple%20of%20report%20columns%20with%20a%20formula%20that%20reaches%20over%20to%20the%20data%20workbook.%20When%20I%20open%20the%20report%20workbook%20and%20click%20through%20the%20update%20boxes%2C%20the%20columns%20using%20the%20formula%20that%20calls%20the%20data%20workbook%20go%20to%20%23REF!.%3C%2FP%3E%3CP%3EGoing%20to%20Data%20on%20the%20ribbon%20and%20clicking%20Edit%20Links%2C%20I%20see%20that%20the%20source%20status%20is%20%E2%80%9CUnknown%E2%80%9D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Brian_Paasch_0-1648742522356.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F360334iDA74D2B39CA1257B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Brian_Paasch_0-1648742522356.png%22%20alt%3D%22Brian_Paasch_0-1648742522356.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20click%20%E2%80%9CCheck%20Status%E2%80%9D%2C%20the%20status%20goes%20to%20%E2%80%9CError%3A%20Worksheet%20not%20found%E2%80%9D.%20(I%20have%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20moved%20the%20file.%20They%20are%20still%20both%20in%20the%20same%20folder%20on%20a%20SharePoint%20resource.)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Brian_Paasch_1-1648742522359.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F360333i6A1D3952BD7216DB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Brian_Paasch_1-1648742522359.png%22%20alt%3D%22Brian_Paasch_1-1648742522359.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20go%20through%20the%20process%20of%20%E2%80%9CChange%20Source%E2%80%A6%E2%80%9D%20and%20point%20again%20to%20the%20source%20file%20location%2C%20it%20still%20won%E2%80%99t%20work.%3C%2FP%3E%3CP%3EHowever%2C%20if%20I%20%3CSTRONG%3Eopen%3C%2FSTRONG%3E%20the%20source%20file%2C%20so%20both%20source%20and%20destination%20files%20are%20open%2C%20then%20everything%20snaps%20together%20and%20the%20formulas%20in%20the%20report%20update%20and%20populate%20the%20summary%20report.%3C%2FP%3E%3CP%3EThat%20seems%20to%20be%20a%20work-around%2C%20tell%20my%20users%20that%20they%20need%20to%20open%20both%20the%20data%20source%20file%20and%20the%20report%20file%20at%20the%20same%20time.%20Seems%20an%20inelegant%20solution.%20(My%20users%20asked%20me%20to%20split%20the%20monthly%20summaries%20from%20the%20raw%20data%20because%20the%20file%20was%20too%20big%20and%20slow.)%3C%2FP%3E%3CP%3EOur%20organization%20migrated%20from%20NAS%20solutions%20to%20SharePoint%20around%20the%20first%20of%20the%20year%2C%20so%20SharePoint%20is%20new%20to%20me.%3C%2FP%3E%3CP%3EAny%20thoughts%2Fsuggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3272834%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Efiles%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3276650%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20file%20can't%20find%20source%20Excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3276650%22%20slang%3D%22en-US%22%3ESince%20nobody%20from%20MS%20answered%20this%20with%20%22that's%20a%20feature%2C%20not%20a%20bug%22%2C%20I%20started%20to%20wonder%20if%20this%20problem%20might%20not%20be%20you%20(SharePoint)%2C%20but%20is%20me.%20But%20%22me%22%20how%2Fwhat%3F%20The%20only%20awful%20thought%20I%20could%20think%20of%20is%20that%20my%20source%2C%20data%20Excel%20file%20was%20corrupted.%20After%20a%20couple%20days%20of%20trying%20hard%20to%20ignore%20that%20possibility%2C%20I%20gave%20up%20and%20began%20the%20task%20of%20rebuilding%20the%20source%20file%20from%20scratch.%20Once%20that%20painful%20task%20was%20complete%2C%20I%20pointed%20the%20report%20file%20to%20the%20new%20source%20file%2C%20and....%20magic!%20Everything%20now%20works.%20Based%20on%20that%2C%20I%20assume%20my%20original%20source%20Excel%20file%20was%20corrupted.%20Painful!%3C%2FLINGO-BODY%3E
Occasional Contributor

Greetings,

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

 

Brian_Paasch_0-1648742522356.png

 

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

Brian_Paasch_1-1648742522359.png

 

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)
Solution
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!