Update values in formulas referencing another workbook upon open

[dsrt16]

I have the following formula place in cell A17 of worksheets 3-31

=PULL("'X:\Staff\AI Program\"&'Set-up Page'!E6&"\["&'Set-up Page'!F6&"]"&C2&"'!A17")

I have to reference folder names, workbook names, and worksheet names by cells since I am creating a template, and the data will change for each teacher.

Anyway, the formula works like a charm; there is just one huge downfall. When I open up TemplateWeek2 which hosts these formulas, it will not update the data until I go into the cell and click enter.

This will not work. I can't have teachers clicking in the cell on each worksheet and clicking enter. The purpose of this program is to save time not make it take longer.

So then I wrote a macro to run upon opening.

Dim i As Integer

    For i = 3 To 31
        Sheets(i).Range("A17").FormulaR1C1 = "=PULL(""'X:\Staff\AI Program\""&'Set-up Page'!R[-11]C[4]&""\[""&'Set-up Page'!R[-11]C[5]&""]""&R[-15]C[2]&""'!A17"")"


    Next i

This macro does the same as me manually clicking on each cell and clicking enter to force the formula to update the values. But it is slow too. And when I get to TemplateWeek9 there will be 8 PULL formulas on each worksheet 3-31, which will be even slower.

This is my first time with formulas referencing closed workbooks. But when I have written formulas to reference open workbooks or other worksheets, they update the results the moment the reference cell changes.

Is there any way to have the cells referecing a closed workbook update automatically without manually pressing enter?

 

[Andrew Poulsom]

No, you can't use concatenation to build a formula. But you can use concatenation to build the string to be passed to the Formula or FormulaR1C1 property in VBA. So enter the formula as it would appear in the cell, then the recorded code can be amended to use your variables.