Forum Discussion
Excel Ref Error online but not on desktop when linking to another table
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?
