Excel: Referencing a cell from another workbook using a dynamic filepath/filename, inside a formula

Question

I have 50 students, each with a gradebook with many scores I am compiling in a master tracker workbook. The data points come from about 60 different worksheets within each student workbook. Rather than have a unique formula for every students' row, I want to use dynamic filepaths/filenames.

This is simplified version of the current set up.

STU_01 - Gradebook.xlsx:

Tab1!A1: 3.35

Workbook 2.xlsx:

A1: 01

B1:

    =IFERROR('C:\Grades\STU_01\[STU_01 - Gradebook.xlsx]Tab1'!$A$1,"")

This correct outputs as "3.35"

Now as I create dynamic filepaths I came up with this:

    ="C:\Grades\STU_" & A1 & "\[STU_" & A1 & " - Gradebook.xlsx]

correctly displays as

    C:\Grades\STU_01\[STU_01 - Gradebook.xlsx]

Question 1: What needs to be done to call for the value of Tab1!$A$1 while maintaining the dynamic filepath/filename?

Question 2: Can the answer to Question 1 be used in a formula, like the IFERROR() I am already using?

Answer 1

You can use VBA to compose a text of your formula and then assign it to the .Formula property of the cell. Something like this:

Sub ActiveFormula()
   Dim tf As String, cel As Range, id As Range
   Application.EnableEvents = False
   Application.DisplayAlerts = False
   Set id = Range("A1", Cells(Rows.Count, "A").End(xlUp))
   On Error Resume Next
   For Each cel In id
      tf = Range("B1").Value & Range("A" & cel.Row).Value & _
      Range("C1").Value & Range("A" & cel.Row).Value & Range("D1").Value
      Range("F" & cel.Row).Formula = tf
   Next cel
   On Error GoTo 0
   Application.DisplayAlerts = True
   Application.EnableEvents = True
End Sub

Here parts of the formula are placed in cells (the first row), however the constant parts may be defined as constants in the code. The results should be in column F, assuming the source files exist.