I partially solved the problem that I initially had and figured that my description of the problem was a bit too detailed. I decided to rewrite my question so it's easier to understand the problem and people who are looking for the same thing can relate faster.
I've got several topic files (each with a different name) with 21 rows and 21 columns that need to be gathered into 1 file (called Summary). In Summary, I want a code that looks at a list of the topic names and then places a reference in the cells to the corresponding cells in the topic file. As you can see in the code below, I've accomplished a simplified version of this. It looks at the cell with the name of the first topic file and then created a reference for all rows and columns in that file.
Sub PullValue()
Dim path, file, sheet
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
path = Worksheets("Settings").Range("B23")
file = Worksheets("Consolidation").Range("A1")
sheet = "Overview"
For i = 2 To 22
For j = 1 To 21
Cells(i, j).Formula = "='" & path & "[" & file & ".xlsm]" & _
sheet & "'!" & Cells(i - 1, j).Address & ""
Next j
Next i
Application.ScreenUpdating = True
End Sub
This works as it should, but after that, it has to do this for all the files in that topic name table. I'll keep on trying, but help would be much appreciated, thanks.
If more info is required, don't hesitate to ask.
Thanks! Bart