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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,640
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top