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]

If you are going to use VBA why not use a normal linked formula and change the source of the link when the workbook is opened or the user updates the cells?

 

[dsrt16]

You lost me, sorry. I am going to explain a little bit clearer, and sorry if you already understood what I was asking, and I just didn't understand your answer.

If by normal linked formula you mean type in the the name of the folder and the name of the workbook instead of having those names referenced by cells, I can't do that.

I am creating a template. Teachers will open the template and input their name in a cell. Now it will save to their last names folder and be called their last nameweek1, lastnameweek2 etc. Anyway it is hard to explain, but I have to reference the folder name, workbook name, and worksheet name by cells.

To make it simpler one example of what the formula could be calling for: X:\Staff\AI Program\Template\[TemplateWeek1.xls]StudentName'!A17

Now when I open TemplateWeek2 that has that formula, I need it to automatically update whatever was in cell A17 of TemplateWeek1. But since TemplateWeek1 will not be open, I have to use the pull function to access closed workbooks.

Right now, I have to click on the cell with the formula and press enter to get it to update. It won't update it automatically.

 

[Andrew Poulsom]

Why can't you use a normal formula in your VBA code. I don't see the need for PULL if you are entering the formulas in VBA.

 

[dsrt16]

Oh, can I write a formula in vba to reference a closed workbook? I don't know how to do that.

The answer to the formula needs to be returned in cell A17 on sheet position 3-31.

So this code would work?

Dim i As Integer

    For i = 3 To 31
        Sheets(i).Range("A17").FormulaR1C1 = "="'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

I don't need pull to reference the closed workbook? Sorry, novice here.

And wouldn't that still be slow....I need it to run relatively quickly upon open.

 

[Andrew Poulsom]

A normal formula will probably be quick than PULL.

 

[dsrt16]

ok, well then is that code right that I wrote? Just take out the pull, but leave everything else the same?

Just A17 = reference?

Dim i As Integer

    For i = 3 To 31
        Sheets(i).Range("A17").FormulaR1C1 = "="'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

 

[Andrew Poulsom]

Did you try it? If it doesn't work post the code you get from recording a macro while entering the formula manually.

 

[dsrt16]

I am confused, sorry!

To enter the formula manually, I would need to use indirect or some sort of formula wouldn't I?

I can't just put in an equal sign, and then type in the formula. I tried to enter it in manually and record it, but the formula will not enter; I just get the error message.

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

Before I had Pull in front of the formula, and it worked. But now you want me to take pull out. And now its not a working formula....

 

[dsrt16]

Since I am using cells to reference the folder name and workbook name, I would need indirect. But since it is a closed workbook, I need pull, right?