workbook only updates links when i open it

All links (be it formula data connection etc) only can evaluate when the workbook opens. Hence, the issue.
There's no way to force evaluation on closed workbook.

AFAIK, there are no workaround for this, workbook that serves as intermediate step must be opened, before final workbook can refresh with current data.
Though PowerBI desktop does work through intermediary Excel to refresh data from source... this isn't available for Excel version of PowerQuery/Get & Transform.

i see your point, but this started only happening when I re-designed the "repo template" which was previously XLS (probably using Excel 97) and then now it's XLSM (now Excel 2010). ever since that change, this issue started happening.

Hmm? That's odd, what type of connection do you use?

It may be that since, xls doesn't use Open XML for file structure, it behaved differently. But then, I'm only familiar with Excel 2010 and up.

yah, it was driving me crazy...i jsut wanted to know why....eventually i convinced my team that the logistics was nonsensical and to just make the "capital" file link directly to the "repo template" file...now I just go to "data" --> "edit links" --> updates values and so it's all good now.

You have 3 files...A linked to B linked to C

In order for A to update with the latest data in B, B needs to be updated (and saved) with the data from C, otherwise, B will still contain the old/un-updated data. There is no work around for this, other than to open B, let it update (automatically), save then close.

Think about it - B wont just know, in it's closed state, that C was updated. It needs to open 1st, then look into C for any updates. If you then close without saving, B will still only contain old data and A will link to the same old data.

This has nothing to do with file types, or anything like that, it is just the way it works. B cannot "see" anything unless it is open, and unless it is saved with the new data (after being opened), it will still show old data

I hope that made sense?

ya I see your point ...just like what the other guy said


yet it was completely working when everything was XLS (ie before I redesigned the repo template file ...in your case...file "c")

I googled this issue and ran into a couple of links that mention version of excel use may cause an issue

I have been using excel for quite a few years now, and I know of no versions where this would work without opening the middle file to update it.

Again, thinking about the logic behind this...
- C gets updated and saved.
- B wont know about the updates unless it gets opened.
- A still shows values from "old" B until B gets opened.
- B gets closed without saving the changes from the links to C
- A will still show the "old" values from B, because B no longer contains the updates, because B was not saved.

don't get me wrong man ..I totally see what you're saying but it was DEFINITELY working before I redesigned "C." (ie the source file).

in other words...before the design ...once C got updated...so did B automatically...and thus A did too

anyway ...the logistics as I said were
totally redundant so nonow A directly links to C

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.