Force Excel to keep sheet name when referencing external tables

[jongar]

Is there a way to force excel from automatically "rewriting" the address of the external reference?

Example
When I mouse over and select a table in another workbook as a source, the formula bar shows this:

=[WorbookName.xlsx]SheetName!TblName

Once I hit enter, the formula bar shows this:

=WorbookName.xlsx!TblName [#Data]

Brackets and sheet names get removed and [#Data] gets added.

I confirmed that excel understands the address with the sheet name by using indirect

=INDIRECT([WorbookName.xlsx]SheetName!TblName)

So its not a limitation by excel.

I need to force excel to keep the table name in the reference because I will be uploading to OneDrive and co-authoring the workbook. When you reference a table in another workbook, and that workbook is closed, the reference is changed to include the web address of your OneDrive like this

=WorbookName.xlsx!TblName [#Data]
=https ://d.docs.live.net/location/FileName.xlsx'!TableName"

I belive this is causing errors, preventing excel from updating the values. when I first open the workbook that contains the links, it will try to update to the latest data. When I use regular ranges, it has no issues. When I use tables, I get "we cant update some of the links in your workbook right now". If you go to edit the links, it looks like this:

If you try to update the values, you are shown this: Notice it is looking for a worksheet, but the file name of the workbook is shown.

Really hoping to find a solution, thanks in advance!

 

[Jon von der Heyden]

Cross-posted here: External reference to other workbooks and co-authoring errors

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.