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

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
997
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:

Forum statistics

Threads
1,078,515
Messages
5,340,861
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top