Hello!
I originally posted a very long and detailed question which may have made it unreasonable to read, sorry!. I decided to summarize my question. Feel free to just read the TLDR and skip right to replying. Full post right below TLDR if you have the time.
TLDR:
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:
Once I hit enter, the formula bar shows this:=[WorbookName.xlsx]SheetName!TblName
Brackets and sheet names get removed and [#Data] gets added.=WorbookName.xlsx!TblName [#Data]
I confirmed that excel understands the address with the sheet name by using indirect
So its not a limitation by excel.INDIRECT([WorbookName.xlsx]SheetName!TblName)
Really hoping to find a solution, thanks in advance!
FULL INFO BELOW [ORIGINAL POST]
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.
If anyone wants to know why I need this, I created another thread where I documented why and how I attempted to fix my issue. I went into a lot of detail and I think it was simply too long and over-complicated as I received 0 replies. Its still good info in case someone is doing research but I created this to simplify my question. If you want to have a look: https://www.excelforum.com/excel-gen...ng-errors.html
Really hoping to find a solution, thanks in advance!
Hoping someone can send me in the right direction. Note that I am using co-authoring on my workbooks. I am having a problem using references to other workbooks (external links) but only if the reference is to a table. I am unable to update to the latest data from the source file without having it open.
I noticed that when you reference a cell, the address looks looks different than when you reference a table
I believe the cause of my issue is the reference lacks sheet name when referencing a table. Interestingly, Excel does recognize a reference to a table when you include the sheet name, but it automatically convert/changes it once you hit enterPlease Login or Register to view this content.
[QCODEUOTE]From this: [FileName.xlsx]SheetName!TableName[ColumnName]
To this: [FileName.xlsx!TableName[ColumnName][/CODE]
The above addresses are like that when the source file is open. Because my issue happens when the source file is closed, please note that the address changes depending on the status of the source file.
I decided to create 2 very simple workbooks (A & B) to test the issue. Please see attached workbooks. Workbook A will be the "source" where I keep a table with some data. In Workbook B I will be testing 4 different reference styles to deduce which one is causing issues and see if I could find one that works best.Please Login or Register to view this content.
With Workbook A closed, we will be opening Workbook B to see if the links update as they should without having to have source file open.Please Login or Register to view this content.
Style 1
seems to give me no issues at all. I open Workbook B and I get a notification asking to update links if trusted. The file is able to be updated. This style is for referencing cells only though, but testing this confirmed there are no issues with connecting to OneDrive nor with using the d.docs.live.net link address as a reference.
Style 2
After opening workbook B, I receive this:
workbook contains links.JPG
When I select "Update", I get this:
we cant update.JPG
After selecting "Edit Links", it looks like this:
source open.JPG
Notice that the type is "Remote File" and the Status is "Error Worksheet not found". If I attempt to select "update values", this window pops up:
excel problem.JPG
The above info seems to indicate that it has found the correct file, however it doesn't know which sheet the source data is at. choosing the correct sheet did not solve the issue.
Style 3
The only thing I could think to force the cell reference was to use indirect function. This is not a solution as it doesn't actually create a link between the files. It simply "prevents" excel from automatically stripping the sheet name from the reference address. However it did improve user experience because while you still have to have the source file open, now you wont get the update notices. I did attempt to use indirect with the http addresss style but I only got a #REF! out of it. This is a better solution than the standard though, as you avoid having to use the menu to update and you get less pop ups. You still need the source file open.
Because the reference (when source file is open) works when you force the sheet name, I think a solution would be to force the reference to keep the sheet name when source file is closed. In other words force it while still keeping the http.d.docs.live.net address.
Another posible solution for my specific needs would be to only have the Source workbook in co-authoring mode, and Workbook B on the local drive (IF excel behaves any differently in this scenario, as far as I know there is no change).
This is partially discussed here with microsoft. Including just for referance https://excel.uservoice.com/forums/2...n-excel-online
I AM UNABLE TO UPLOAD BOTH FILES
CAN SOMEONE PLEASE HELP ME FIGURE OUT HOW TO SHARE THEM?
WorkbookA is very simple, here is a picture of the contents. (file I couldlt upload). Sheet1 has the range, Sheet2 has the table.
WorkbookA data.JPG
Bookmarks