2

I have on a local folder a source file with a two-column table with key-value pairs and a destination file which uses data from the source as follows:

  1. link to a specific cell:

    ='C:\Temp[source.xlsx]Sheet1'!$B1

  2. query a value from a range:

    =VLOOKUP(A1,'C:\Temp[source.xlsx]Sheet1'!$A$1:$B$6,2,0)

  3. SUMIFS function with range and condition:

    =SUMIFS('C:\Temp[source.xlsx]Sheet1'!$B$1:$B$6,'C:\Temp[source.xlsx]Sheet1'!$A$1:$A$6,D1)

When opening the destination workbook without opening the source workbook I get the "This workbook contains links to ..." message with "Update" and "Do not update" options. In the background of this prompt I can see the values saved when I closed the file.

If the source file remains closed and I choose "Update" option I get correct values for the link (1) and for the query (2) but #VALUE! error for SUMIFS (3). If I open now the source file then the SUMIFS value is correctly calculated.

Please note that - without the source file opened - in the "Edit Links" dialog (from Data folder) I get first an "unknown" status for source file, then "OK" after I click "check status" and still #VALUE after I click "update values"

This is the test case I used for a work-related situation: a file with SUMIFS function with arguments pointing to a source file which shows correct value when prompted for update/do not update but changes to #VALUE! error regardless of the option I choose (to update or not to update)

The obvious questions: why Excel 2013 is doing this and how to solve it?

Sebastian Deac
  • 21

1 Answers1

1

Excel is working as designed. It does not allow formulas to read data in closed workbooks.

To work around this limitation, you will need to use VBA to retrieve data from the closed files. You can also search for "excel 2013 closed workbook data" to find other methods.

Here is an example of how to accomplish it from Read information from a closed workbook using VBA in Microsoft Excel:

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("C:\Foldername\Filename.xls", True, True) 
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("TargetSheetName")
        ' read data from the source workbook
        .Range("A10").Formula = wb.Worksheets("SourceSheetName").Range("A10").Formula
        .Range("A11").Formula = wb.Worksheets("SourceSheetName").Range("A20").Formula
        .Range("A12").Formula = wb.Worksheets("SourceSheetName").Range("A30").Formula
        .Range("A13").Formula = wb.Worksheets("SourceSheetName").Range("A40").Formula
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Here is another example - Excel “Pull” Function: Creating dynamic links to closed workbooks.

You will have to experiment with this to fit your own needs.

CharlieRB
  • 22,754