Forum Discussion
Excel Ref Error online but not on desktop when linking to another table
Hi,
So I have 2 seperate spreadsheets and I am linking Table1 from Spreadsheet1 onto Spreadsheet 2. When I do this with Excel desktop it is able to import the data without any issues. The new table in Spreadsheet 2 automatically updates as I enter data into Table 1.
Both of these sheet files sit on my one drive.
Now when I save, close the files and open up the excel files online via sharepoint, there is a Ref error in the Spreadsheet 2. The formula has changed to the sharepoint location which I assume is correct. Can somebody please advise?
4 Replies
I might be crazy but I have 2 sets of linked workbooks. One works online, the other doesn't. It's the same data in both sets.
- the linked workbooks that work online without ref errors don't have tables / structured references & I didn't use power query to clean up the data.
- The linked workbooks with REF errors do. The links 'seem' valid. Same links as the working version, but Ref errors all over the place.
It depends on which kind of links were generated in Excel desktop. It could be links on mapped OneDrive, or extended OneDrive/SharePoint links, whatever. I'd suggest to open both file in Excel for web, add links, with that they shall work both in Excel for web and Excel desktop.
This issue is common when working with linked tables across different Excel files in OneDrive/SharePoint because Excel for Web handles external references differently than the Excel desktop app.
Excel Desktop Supports Full External Links
When linking a table from Spreadsheet1 to Spreadsheet2, Excel desktop keeps the reference and updates it automatically.
Excel for Web Does Not Fully Support External Links
When you open Spreadsheet2 in Excel for Web, the formula tries to reference the SharePoint URL instead of the original desktop-style file path.
Excel for Web does not always support dynamic external links, causing the #REF! error.
How to Fix This?
Option 1: Use PowerQuery for Linking Data
Instead of using direct cell references, use Power Query, which works well in both Excel Desktop and Web.
Option 2: Use SharePoint-Supported External Links
If PowerQuery is not an option.
In my opinion, the best long-term solution is Power Query because it allows real-time table linking without interrupting Excel Web...but that's just my opinion.
Hope this helps! 😊
Thanks for the reply.
What would be the best option for using Power Query? I understand I can use the desktop app and try to Get Data and then select an Excel workbook. However, I am not the owner of this Excel workbook. It is an excel file that sits on a shared one drive that I access via excel desktop. The PowerQuery only allows me to select local files.
What would be the best way for the owner of the excel file to output their table so that others in the organization have a synced version of this table?
