+ Reply to Thread
Results 1 to 5 of 5

External reference to other workbooks and co-authoring errors

  1. #1
    Registered User
    Join Date
    12-16-2015
    Location
    East Coast
    MS-Off Ver
    2016
    Posts
    33

    External reference to other workbooks and co-authoring errors

    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:
    =[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.

    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
    Please Login or Register  to view this content.
    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 enter
    [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.
    Please Login or Register  to view this content.
    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.

    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
    Last edited by jonagpa; 02-20-2020 at 12:40 PM. Reason: Added TLDR summary

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: External reference to other workbooks and co-authoring errors

    To bump the thread, you should have added the new information to a new post in this thread. This post of mine will bump the thread for you.

    New information here (for the benefit of anyone reading this):


    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.

    Really hoping to find a solution, thanks in advance!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-16-2015
    Location
    East Coast
    MS-Off Ver
    2016
    Posts
    33

    Re: External reference to other workbooks and co-authoring errors

    Updated the OP, Added TLDR. OP may have been unreasonable to read (too much unnecessary info). I apologize if anyone read the OP

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: External reference to other workbooks and co-authoring errors

    I didn't ... Too much information!

    Hopefully you'll get a bite now.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: External reference to other workbooks and co-authoring errors

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] External files cause errors, how do I skip them?
    By gregersdk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2019, 09:40 AM
  2. [SOLVED] External Links result in #VALUE errors when external file is closed
    By ziggyztz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2019, 04:41 PM
  3. How to get rid of #Ref! errors when formula refers to external workbooks
    By ibuhary in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-24-2017, 11:25 AM
  4. How to reference external workbooks using only a string
    By eoghanf17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-15-2017, 04:08 AM
  5. External Reference and linking with workbooks
    By ervarunkant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2014, 07:16 AM
  6. reference errors linking to other workbooks
    By Ltat42a in forum Excel General
    Replies: 2
    Last Post: 11-25-2007, 02:42 AM
  7. External Web Query Errors
    By tokyo4tokyo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-24-2006, 11:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1