How does referencing cells in another workbook work?

[JumboCactuar]

Hi,
Just want an understanding on how excel pulls values from another workbook when you reference in a formula like this:

=SUM(D:\Reports\[Sales.xlsx]Jan!B2:B5)

As on our network drive at work, a 30mb spreadsheet would be fairly slow to open, yet you can pull values from it almost instantly with the formula.

 

[ParamRay]

a 30mb spreadsheet would be fairly slow to open, yet you can pull values from it almost instantly with the formula

Yes, but the values in the workbook containing the linked formulas only get updated under certain conditions, e.g.

1) You click the Update button when opening the workbook;
2) You force the values to be updated, via Data --> Edit Links --> Update Values;
3) You enable automatic update of links on startup; or
4) The linked workbook is open at the same time (and in the same Excel instance pre-2013 version).

 

[JumboCactuar]

Yes, but the values in the workbook containing the linked formulas only get updated under certain conditions, e.g.

1) You click the Update button when opening the workbook;
2) You force the values to be updated, via Data --> Edit Links --> Update Values;
3) You enable automatic update of links on startup; or
4) The linked workbook is open at the same time (and in the same Excel instance pre-2013 version).

Can't you just delete the formulas and re add them with VBA?

 

[ParamRay]

Can't you just delete the formulas and re add them with VBA?

Not sure I see your point... If you delete and re-add the formulas, you're still restricted by the same items above.

 

[JumboCactuar]

Not sure I see your point... If you delete and re-add the formulas, you're still restricted by the same items above.

sorry if i wasn't clear but if for example i run the following macro:

Sub Macro2()
Dim Sht As String


Sht = Range("Z1")


    Range("A2:B1000").Cells.ClearContents
    
    Range("A2").FormulaR1C1 = "='C:\test\[mytest.xlsb]" & Sht & "'!R[4]C21"
    Range("A2").AutoFill Destination:=Range("A2:A1000"), Type:=xlFillDefault
    
    Range("B2").FormulaR1C1 = "='C:\test\[mytest.xlsb]" & Sht & "'!R[4]C18"
    Range("B2").AutoFill Destination:=Range("B2:B1000"), Type:=xlFillDefault


    Range("A2:B1000").Value2 = Range("A2:B1000").Value2
End Sub

it instantly grabs the values a lot quicker than opening the workbook to view them, im guessing excel can load them differently.