I was working with VLOOKUP using multiple files in a network. Basically I have 3 files to link. 1) the main file - where the user encodes the data 2) the monthly summary - with which data are from the main file and 3) the annual report - data are from the monthly summary. VLOOKUP worked fine. The problem comes when changes are being made to the main file. The values on the annual report file won't update unless I keep open the monthly summary file. Is there a way to correct this?

Hi, You could add a routine to automatically open then close any linked Excel files in the Annual Report workbook. The following routine will do this, its fairly basic with no error checking. If a linked file was already open it will probably open a dialog box asking if you want to reopen the file. If this approach works for you, you could add a check for an already open file. Put the following code in a standard VBA module in the Annual Report workbook Sub UpdateLinks() Dim varLinks As Variant Dim strChar As String Dim strWBName As String Dim i As Integer Dim n As Integer 'get all Excel workbook links into an array varLinks = ActiveWorkbook.LinkSources(xlExcelLinks) 'get each workbook name (and path) and open it For i = 1 To UBound(varLinks) Workbooks.Open (varLinks(i)) 'have an empty string ready for the filename (without path) strWBName = "" 'get the filename by working backwards until a "\" character For n = Len(varLinks(i)) To 1 Step -1 strChar = Mid(varLinks(i), n, 1) If strChar <> "\" Then strWBName = strChar & strWBName End If If strChar = "\" Then Exit For Next n 'close the workbook we just opened Workbooks(strWBName).Close SaveChanges:=True Next i End Sub Put a button on a suitable toolbar and link to this routine, and click the button to run it whenever updating the Annual Report, or you could make this run whenever the Annual Report workbook opens. To do that, put the code in the 'ThisWorkbook' module in the Workbook_Open() routine (omit the' Sub UpdateLinks() 'and' End Sub' statements from the suggested code as the Workbook_Open() routine provides the name of the routine and the 'End Sub'). Hope this works or gives you some further ideas for solving your problem.