Force Excel to keep sheet name when referencing external tables

Question

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 and forcing excel to use the full reference

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

So its not a limitation by excel. However this only proved that excel can handle that style of reference. The problem is that when you use indirect, youre not actually creating a "link" to the source file, your workbook will never ask to update the values if the source file is closed.

---

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

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

I believe 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: Edit Link

If you try to update the values, you are shown this, Notice it is looking for a worksheet but the name its expecting is actually the name of the workbook. Select Sheet

I believe this is because when you reference a range, the address is written like this:

Rance referance =https ://d.docs.live.net/Loacation/[FileName.xlsx]SheetName!$A$1:$D$20
table referance =https ://d.docs.live.net/location/FileName.xlsx'!TableName

Notice there is a difference in the location of exclamation ! point. I believe this is the reason why excel shows the workbook name as the worksheet is looking for. again if you use indirect, youre able to force excel to use the "long" address, so we know excel recognizes it with no issues.

Really hoping to find a solution, thanks in advance!

Answer 1

This is an alternative to what you're trying to do.

I did some tests and if the file is saved in OneDrive, you could have it open (from OneDrive not locally) and just refresh the data with PowerQuery.

Steps to create the query:

In Chrome:

1. Locate the source file in Onedrive (aka: https://onedrive.live.com/)
2. In google Chrome, download the file (we need to get the download link in the next step)
3. Click the elipsis in Chrome's top right corner and click on Downloads (or press Ctrl + J)
4. Right click the link of the downloaded file, and select Copy

In Excel...

5. Click Data | Get Data | From other sources | From web (I don't have the english version right now, but you should be able to locate it)
6. Paste the URL
7. Click Ok
8. Select the Sheet/Table (do any transformation you may require)
9. Click Close and Load to...
10. Select a sheet or any location
11. Press Ok

Now do any formulation against that table

As said, I did some tests with the file opened in another instance and then refreshing the table, and it worked.

Let me know if yours do.

---

EDIT:

As per your comments, (though I didn't test it) I did some research on how to make this work with the default sharing links.

Here are the steps:

In Chrome:

1. Locate and select the source file in Onedrive (aka: https://onedrive.live.com/)
2. Click on Share and generate the view/edit link
3. Copy that link

As of this date, the link should look something like this:

https://1drv.ms/x/s!ArAKssDW3T7wnIIEvmhHrMxfvhowww?e=UsaATm 

The key here is to replace the ms with ws, so the link that you need to use in Excel should be something like this:

https://1drv.ws/x/s!ArAKssDW3T7wnIIEvmhHrMxfvhowww?e=UsaATm 

In Excel:

1. Click Data | Get Data | From other sources | From web (I don't have the english version right now, but you should be able to locate it)
2. Paste the URL
3. Click OK
4. Righ click the icon that says 1dr.ws
5. Select and click Excel
6. Continue with the other transformations