Connected sheets not updating

Hi Kristian,

It was a simple example to show you how a macro typically works. In your case provided your workbook is in Automatic calculation mode then it will update from the 'NEW Accom Log 2014.xlsx' without you needing to do anything.
You say you have to click the 'Update Values' button yet I see no such button so I'm not sure what you mean. Could you clarify what you mean by update values?

However how is the log being changed? As I understand it it's updated by several individuals at different times. That has to be the case since Excel is a single user system (ignoring the shared workbook functionality which we needn't discuss here since it will complicate things and really is not to be recommended). i.e. it has to be saved and closed first before another user can open it. And herein lies your difficulty.

As mentioned previously both workbooks must be open in memory for the links in your overview workbook to be able to work. Once it's open no one else of course can change it. So at the moment if the user of the Collective Data workbook was the first to open the log workbook, no one else may open it other than in Read Only mode, which means they can't change it. The log workbook needs to be closed before a new user may open and change it.

However make sure that your Excel Application settings are set to allow automatic updating of links. I suspect yours may not. To check the setting click the Office button on the Ribbon, pick Excel Options at the bottom, click 'Trust Center' and then 'Trust Center Settings' and then 'External Content'. What do you see for Security Settings for Workbook links? Make sure the middle option for 'Prompt user on automatic update for Workbook links' is selected.

Whilst we're here select the 'Macro Settings' since you will be using macros in this workbook. Again make sure the 'Disable all macros with notification' is set.

When you next open the Collective Data workbook you will be presented with two warnings asking if you want to Enable macros and enable external links. Answer yes to both those.

You could put the Collective log in what's known as a trusted location and then you wouldn't see the warning about macros but perhaps leave that for later.

The attached therefore contains the following macros. First note that there is a cell in which you can change the frequency of the automatic update and a cell which dictates whether the macro runs at all. This is set to Y(es) when you click the 'Stop Update process' button.

Sub UpdateFromLog()
    Application.DisplayAlerts = False
    If Range("Stop") = "Y" Then End
    Workbooks.Open Filename:="NEW ACCOM LOG 2014.xlsx", Notify:=False
    ActiveWindow.Close
    Call OnTimeMacro
    Range("Stop") = "N"
End Sub

This macro first checks for a Y in the 'Stop' cell and ends the macro if it's there. Then the macro opens and closes the log workbook thus updating the links.

Finally it calls the 'OnTime' Macro below

Sub OnTimeMacro()
    Application.OnTime Now + TimeValue("00:" & CStr(Range("Interval")) & "00:"), "UpdateFromLog"
End Sub

*
This pauses the update process for the interval you have entered in the frequency of update cell. When the interval has passed this macro directs control back to the 'UpdateFromLog' macro which starts the whole process off again.

Click the 'Start Update Process' to kick the thing off.

Hope this helps.