Linked cells will update only when I save the source

gifer

New Member
Joined
Nov 26, 2005
Messages
8
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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:

Code:
Sub Test()

Application.CalculateFull

End Sub

I hope this helps :confused:

Best regards

Richard
 
Upvote 0
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... :rolleyes:

Thanks for your help.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,880
Members
447,883
Latest member
Hgiang0101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top