-1

My question is about issues with linked formulae in Excel spreadsheets.

I have a complicated structure of linked Excel Spreadsheets updated each month for Financial reporting.

The problem I have is that I will often update the values in source files. The links feed through correctly. Then I save all the files with the updated values in source and target files.

The problem arises when I reopen the files at a later time Excel forgets the revised figures. Then I need to go back and open the source files to make it pick up the new values again.

This problem is getting worse and worse, happening more and more for no apparent reason.I seem to spend half my time re-opening source files to update the linked values. It seems like the Internal Table that Excel keeps for links is remembering old values and not saving the new one ones when I update the table.

So my question is this - how do I stop Excel doing this, how do I force it to remember the new linked values when they change in the source sheet and stop it from reverting to previous remembered values ?

To be clear, every time I do this I save everything in sight, but it makes no difference.

I have also tried to relinking to a different copy of a file and then linking back. This works sometimes, but not always.

Kieran
  • 1
  • 1
  • It could be as simple as a security setting. When you first open the target file, do you get a dialog pop up: "`This workbook contains links to one or more external resources that could be unsafe. If you trust the links, update them to get the latest data. Otherwise you can keep working with the data you have`" with buttons `[Update]` `[Don't Update]` `[Help]` ...? – ashleedawg Apr 10 '18 at 11:23

1 Answers1

0

If you're not getting this warning every time you open the 'target' workbook:

img

...then there's a good chance the problem is that, at some point, automatic update of external links was disabled.

To re-enable it:

  1. On the Data tab, in the Connections group, click Edit Links

  2. Click the Startup Prompt button

  3. Choose the 1st or 3rd option.

  4. Close & save all open workbooks and try it again.

    • If you chose the third option in step 3, all links should update automatically.

    • If you chose the first option in step 3, you will be asked if you want to update the links.

img

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • I don't think this is the cause of the problem. I am already fully aware of this setting, indeed I have deliberately switched off automatic updated because if it is switched on it makes files take much too long to open and creates a false sense of security as it will frequently update with the wrong values or not at all with this setting on. – Kieran Apr 11 '18 at 11:37
  • @Kieran - but **if you disabled automatic link updates**, then the linked formulas don't update - **which is the cause of your problem!** If the formulas take too long to update then they should be made more efficient (managed in the `Edit Links` dialog). But you can't have it both ways: *either you want your formulae to update automatically, or you don't.* That **is** the cause of your problem. – ashleedawg Apr 11 '18 at 11:48
  • Yes but surely, if I have already updated the links and saved them down in full, then there isn't a need for the file to "update" them again when it opens - it should already know, becuase I have already updated them and nothing has changed in the source file. – Kieran Apr 12 '18 at 14:18