Linked cells will update only when I save the source

[gifer]

Hi. I have two files where one is dependent on the other via a few linked cells. The source file is going out with Get External Data to populate its cells, and then running simple formulas with that data. The dependent file is basically the same file as the source file, but the cells only have links to the source file rather than formulas.

When the source file refreshes with new data from the Internet, I want the cells in the dependent file to change automatically. The only way I can get the dependent file to update is to save the source file after it has updated, and then manually update the dependent file going to Edit/Links… Update.

I have 'Automatic' selected in the Links options box.

Under Tools/Options/Calculation I have Iterations checked, Update remote references checked, and Save external link values checked. (My formulas require Iterations to be checked).

It doesn't matter if I have both workbooks open or not, nor does it matter if I have two instances of Excel open with one workbook in each instance.

I can make this work by using a macro to Save the source file after every refresh, AND also a macro to run Links/Update in the dependent file. But if the source file happens to be Saving at the same time the dependent file is trying to Update, the dependent file presents the File/Open because it can't locate the source file when the source file is being saved. If I can work around this conflict, this method will be OK. But I really don’t understand the need of remote reference if you have to save the source file to get it to work.

Any ideas?

Excel2000 SP-3 Windows2000 SP-4

 

[Richard Schollar]

Hi Gifer

Welcome to the Board!

This is untested, so i am afraid I am unsure as to whether it will solve/improve your current situation.

Rather than having a macro to save your source workbook, have one that forces a global recalculation (it's very simple code) following update of your source book:

Sub Test()

Application.CalculateFull

End Sub

I hope this helps

Best regards

Richard

 

[gifer]

Thanks... It didn't work, at least the way I used it.

I placed it after Application.ActiveWorkbook.RefreshAll in the source workbook macro.

Since I didn't know if it was supposed go in the source book or the target, I put the same line of code in the target code after the Updatelink code line. Both ran without errors, but no change to the cells in the target file.

I'm sure I'm doing something simple that is wrong or something simple is setup incorrectly...

Thanks for your help.

 

[Richard Schollar]

I'm afraid if it was going to work, what you did would have done it - so it's a no go.

By the way, what version of Excel are you using? I used to hve a similar-ish situation in Excel97 with formulas referencing off of Pivot Tables (it was a recognised problem, corrected (so MS claimed) in following versions of Excel). One workaround was to run a Replace command on the formulas that aren't updating in the dependent file (replacing the '=' at the start of the formula with an '=' - this works in a similar way to clicking on one of your dependent formulas following a refresh, hitting F2 and then pressing Enter, which i presume will update the cell value) which would force the new values to be updated. Not an elegant solution, but it should work.

Hopefully, somebody else will have a better (and prettier!) answer.

Best regards

Richard

 

[gifer]

Thanks! After I replied the first time, I did another search of the forum files using a different key word(s). I found out that this is a common problem. Not described exacatly like mine, but many, many, fustrated people trying to get "Update now" to work. Most of the posts were 2003/2004 date. There were NO solutions in all the ones that I read!

I am using Excel2000 SP-3

Thanks for giving it a shot!