Results 1 to 4 of 4

Thread: Updating Links

  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Links

    I have 2 old spreadsheets (.xls) that link to each other
    They update their information from the other file when you open one of them.
    The file that the information is being updated from doesn't need to be open.
    Both files are in the same folder

    I tried re-create this using 2 new spreadsheets (.xlsx)
    But when I open one of the files I get "This work book contains one or more links that cannot be updated..."
    So for the information to be updated they both need to be open.

    What is the setting that is in the old spreadsheets that allowed the info to be updated without opening the file.
    Or is this a limitation with .xlsx files

    Regards Paul

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Do you happen to have named ranges? I have had some quirkiness updating files from one spreadsheet to another that seemed to disappear when I deleted the names.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply Maudibe

    There are no named ranges in the new spreadsheets.
    There are named ranges in the old one which is the one that works.

    I am thinking of just writing a macro to open the other sheet, let it update and then close it.
    But it would be nicer to not have any macros in it.

  4. #4
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi,

    From what I know this error message appears only when link reference no longer exists. Misleading in here is that when you open the source file first, the error message does not appear, but dont be fulled - the link remains invalid. I would try to find the the invalid link first. Matter is slightly complicated when link refers to named range, but... you can still find it.

    I would start with searching for exclamation mark in the entire workbook. Open both sheets, go to the "new sheet" as you call it, "Find", click options, in dropdown to the left change sheet into workbook and click find all. In search results look for #NAME? under "value" (or any error value for that matter).

    If link does not refer to named range but to a cell instead, repeat the above steps searching for [ (square bracket) instead of !

    if this doesn't work then check source data in your charts if you have any in your sheet.

    hope this will help

Tags for this Thread

Posting Permissions

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