Excel – How to update automatically and continuously cell values in the master workbook, while the source workbooks are edited from different computers

microsoft excelmicrosoft-excel-2007

I need to make a master file which can be used to supervise the daily actions of the members of the team. By doing so, the master file uses numerous countif and countifs functions to calculate the unsolved items and other things. The data used by the functions comes from other tabs within the same workbook and those data comes from numerous different excel files. (see below)

Flow chart Appendix

My problem is the following: The data that can be found in the Master file’s Country# tabs’ cell do not refresh continuously whenever a member of a team changes the related cell in the source file. I can refresh the cells in the Master manually through the ribbon (Data/Connections/Edit links/Update values), either by closing and reopening the Master document, or by opening the source workbooks in the background but the goal is to make it up to date all day without any intervention.

Please note that unfilled cells in the source files are already linked to the master file, to be able to see further changes in the source files. (Example: in Country3.xlsm only 3 items can be found in B5:B7. B8, B9, etc are already linked to the corresponding cells in the Master file, so if a 4th item is inserted, it should be seen in the Master file.)

I use Excel 2007 and my settings are the followings:

  • Excel options/Formulas/Workbook Calculation: Automatic
  • Excel options/Advanced/When calculating this workbook: Update links to other documents
  • Excel options/Advanced/When calculating this workbook: Save external link values
  • Excel options/Trust center/Trust center Settings/External Content/ Security settings for Data connections: Enable all Data Connections (not recommended)
  • Excel options/Trust center/Trust center Settings/External Content/ Security settings for Workbook Links: Enable automatic update for all Workbook Links (not recommended)
  • Excel options/Trust center/Trust center Settings/Message bar/Showing the Message Bar: Show the Message Bar in all applications when the content has been blocked

Please help me solve this problem!

Best Answer

Nice formatting of your question!

There is no way to turn that on, but there are ways to get it to do it automatically. Enable the developer ribbon if you haven't already (if you have full Excel) and in that ribbon click "Visual Basic"

You want to stick ActiveWorkbook.RefreshAll somewhere. Maybe by editing 'ThisWorkbook' and adding the following code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveWorkbook.RefreshAll
End Sub

so that it happens every time you switch tabs?

Or maybe by editing the master sheet's VBA code and adding ActiveWorkbook.RefreshAll somewhere in there, like in the Worksheet's Worksheet_Activate or Worksheet_SelectionChange events?

Once you get that working you could also try going to https://stackoverflow.com/questions/211715/how-to-make-a-macro-which-executes-periodically-in-excel to have it periodically update every n seconds instead.

Related Question