• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

External links not updated

J

Jovica

New Member
My workbook is connected to 10 cells from 10 other wbooks (1 cell per source workbook). Not only that linked cells are not updated (even though Edit links suggests so), but when I open one of source books (in that case linked cell is updated - temporarily) and with updated cell value I save that main
workbook, next time I open it there stands an old value that was before update.
It looks like Excel using some cache sources even if external links are updated (as I've mentioned that can only be done by clicking on open source in Edit links).

If my post is confusing I'll give an example :
MAIN workbook A1 - connected with workbook SOURCE
A1 Value = 500
Source Value = 100

after Open source in Edit links in MAIN workbook, everything is OK (this is my first problem, that links are not updated automatically - I've tried everything in options and nothing helps ).
Now A1 = 100
Save MAIN workbook.

Open MAIN workbook
A1 = 500 (I don't know what's going on, value of 500 is nowhere to be found!)

Any help would be appreciated.
 
When you open the main work book excel puts a Security Alert to Enable the Automatic Updation. If you enable the option, then you are done. Hope the reply helped u
 
I've tried all options in Edit links / startup prompt and the problem is still there : update only possible with Open source and old value "returns" after I save/reopen the workbook.
 
Hi, Jovica!
Could you post the cell contents (formula, and the same for all other dependent cells too) that refers to other workbooks? Otherwise consider uploading a set of sample file + a couple of linked files.
Regards!
 
Sorry, not possible without uploading source file (belongs to company where I work). I am trying to create some external file that is going to gather values from various workbook and generate text.

Value from source workbooks are not just simple values that I input, they are results of a chain of formulas, one of them is sort. I am also using custom concatenate function (joins all values from a range). But that should be a problem I guess.

-------------------
Maybe it's because that concatenate function, now I found that it doesn't update (automatically calculate) even with Application.Volatile added.
I'll test that...
 
Hi Jovica,

Check if the below options are enabled in your Excel.

1) File -> Options -> Advanced -> Under "When Calculating this workbook" section, tick the option "Save External link values" & "Update links to other documents"
2) File -> Options -> Advanced -> Under "General" section. tick the option "Ask to update automatic links"
3) Enable Calculation Options to Automatic under Formulas ribbon.
 
Thank you guys for your help.

Problem was in user defined function. It recalculates only if I refresh a cell in source workbook, even with the Application.Volatile on.
Since I actively work with source workbooks calculations are done by effect of that, but when the MAIN workbook (which gathers values from SOURCES) is opened Excel somehow fetches some old values since UDF is not refreshed.

Added Workbook Change event and now everything is fine.
 
You must log in or register to reply here.
Back
Top