Excel 365 ProPlus - saved links giving 'old' values?

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
I have a model that generates charts for PowerPoint. The values in the table for the chart are linked to another workbook.
Each day the models are copied to a new folder with the date in, as we need to keep a close audit trail.

So the value in one of the cells could be
='F:\3 Year Planning\14-10-2019\[Strategic cost forecast Assumptions.xlsb]Constants'!O22 giving the result of 47
A future day would be
='F:\3 Year Planning\14-11-2019\[Strategic cost forecast Assumptions.xlsb]Constants'!O22 giving the result of 49
and the next day would be
='F:\3 Year Planning\15-11-2019\[Strategic cost forecast Assumptions.xlsb]Constants'!O22 giving the result of 49

The workbook is set to not update linked values or update links on calculation, as there are a lot of files linked to, so there is a macro that opens each file in turn then each linked file, does a CALCULATE and a DoEvents function, saves them then shuts them down. This is to ensure that all links are updated in the right order.

Mostly it works fine, however on some occasions when the file on the 15th has been opened and saved and closed a number of times, showing the result of 49, on the odd occasion when it's opened it reverts to 47. After a period of time, when it has decided to calculate it will swap back to 49. It seems to keep a history of what that number had been in different 'saves'

Unfortunately someone opened the file on 14-11 and copied a chart where the value had reverted back to 47 (before it had done a calculate), without realising. This was distributed to the Board of Directors, and it was highly embarrassing to have to resend it the next day correcting it.
 
Last edited:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,089,850
Messages
5,410,770
Members
403,329
Latest member
IR84

This Week's Hot Topics

Top