Johnny C
Well-known Member
- Joined
- Nov 7, 2006
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
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.
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:
