+ Reply to Thread
Results 1 to 8 of 8

External link update error: require the source file to open

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    External link update error: require the source file to open

    Dear all,

    I have a file which has external links. I tried to make the named ranges in the source file as:

    Please Login or Register  to view this content.
    But this caused a problem: OFFSET does not support this, as it required the source file to be opened first before data from the named range can be fetched into a new file.

    Then I searched online which said the INDEX function can overcome this problem. I tried again as this:

    Please Login or Register  to view this content.
    But this still showed error for the externally linked data if the source file is not opened first.

    Is there anyway to get this to work?
    Last edited by BNCOXUK; 01-23-2018 at 08:51 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: External link update error: require the source file to open

    Please Login or Register  to view this content.
    You cant do that even if the file was open.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: External link update error: require the source file to open

    My question is: if the named range is defined with OFFSET or INDEX in the source file, then every time the source file has to be opened before data can be fetched from these named ranges. That is my question. Is there a way to avoid having to open the source file?

  4. #4
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: External link update error: require the source file to open

    Short answer is no, you'll either have to open the source file manually or open/update/close silently with VBA.
    Design everything to be as simple as possible, but no simpler.

  5. #5
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: External link update error: require the source file to open

    Basically for my case, the original named ranges in the source file are defined like: =Sheet1!$A$6:$A$1000. Let's call this: TickerID.

    Now if I simply link to this file, it is fine. But later someone mistakenly deleted some rows at the bottom area of this named range, and the range referred to will become something like =Sheet1!$A$6:$A$123.

    Now my file will have error because the original named range has been changed.

    For this reason, I wanted to use some static range like: =OFFSET(Sheet1!$A$6,0,0,1000,1), or =Sheet1!$A$6:INDEX(Sheet1!$A:$A,1000). But now I have to manually open the source file before data can be fetched.

    So there is really no way to get around this?

  6. #6
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: External link update error: require the source file to open

    Does anyone have an idea? I cannot believe Excel cannot handle this.

  7. #7
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: External link update error: require the source file to open

    Sorry, there is no way around it. Even some complicated SUMIFS etc. start requiring you to open the source file to get the data. I encounter this all the time.

  8. #8
    Forum Contributor
    Join Date
    06-18-2012
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    347

    Re: External link update error: require the source file to open

    Many thanks, Stormin. I totally give up now. Shame Excel cannot handle this case which should be simple really.

+ 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. Accessing data in an external link when you dont have the source file
    By JFNORDEN in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2017, 08:09 PM
  2. Must open external file to link formula
    By Bond007 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-17-2014, 11:03 AM
  3. Warning: Open source to update values (error message
    By kucker64 in forum Excel General
    Replies: 0
    Last Post: 04-25-2014, 11:12 AM
  4. #NAME? error when external file is open
    By louisv1123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2013, 06:49 PM
  5. [SOLVED] Why this formula only works when the external link file is open
    By zjianguk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2013, 06:18 AM
  6. linked cells will not update unless source file is open
    By merlyn45 in forum Excel General
    Replies: 6
    Last Post: 05-18-2012, 03:43 PM
  7. Update workbook without open XML source file
    By vietdieu in forum Excel General
    Replies: 0
    Last Post: 01-24-2012, 11:39 AM

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