Screenshots are such little help... The second screenshot tells me exactly nothing.
The third: again, screenshots are so limited. The path you're typing in your attempted formula doesn't match the path in screenshot 1. I can't tell why you need to have a [Date to txt] column.
To refer to a fixed cell in a variety of workbooks, using a workbook name (partially) stored in another cell, you could use the INDIRECT function; but that will not work with closed workbooks, so doesn't meet your requirement.
You could write a quick bit of VBA, to loop through the output cells in Column C, and write in the formula which will refer to the closed workbooks:
Sub foo()
Dim l As Long
Dim s As String
With Sheet1
For l = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
s = "='" & Environ("UserProfile") & _
"\Documents\My Data\Daily Reports\[" & _
Format(.Cells(l, 1).Value, "dd-mm-yy") & _
".xlsx]Financials'!$D$5"
.Cells(l, 3).Formula = s
Next l
End With
End Sub
Bookmarks