使用INDIRECT函数动态引用外部Excel文件时遇#REF错误的技术问询

尝试制作基于日期动态引用外部Excel文件的公式，将日期格式化为ddmmyy格式后，使用公式时出现#REF错误。

静态公式可正常运行：

=IFERROR(COUNTIFS('R:\hal 6\nicarm\LijstenPerDagExcel\[230924.xlsx]Data'!$V$2:$V$999;
">=01/01/1970  22:00:00";'R:\hal 6\nicarm\LijstenPerDagExcel\[230924.xlsx]Data'!$A$2:$A$999;"LA")
+COUNTIFS('R:\hal 6\nicarm\LijstenPerDagExcel\[240924.xlsx]Data'!$V$2:$V$999;
"<=01/01/1970  06:00:00";'R:\hal 6\nicarm\LijstenPerDagExcel\[240924.xlsx]Data'!$A$2:$A$999;"LA");""))

但尝试用INDIRECT函数将路径设为变量时出现错误。

补充说明：通过VBA打开外部工作簿的方式可使COUNTIFS正常工作，当前VBA代码较为基础，未使用循环，仅作为临时方案：

Private Sub Workbook_Open()
Dim wkb As Workbook
Dim wkb2 As Workbook
Application.ScreenUpdating = False
Application.Cursor = xlWait
Set wkb = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\180924.xlsx")
Set wkb2 = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\190924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\200924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\210924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\220924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\230924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\240924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\250924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\260924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\270924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\280924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\290924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:\hal 6\nicarm\LijstenPerDagExcel\300924.xlsx")
wkb2.Close (False)
wkb.Close (False)
Application.ScreenUpdating = True
Application.Cursor = xlDefault
End Sub

内容的提问来源于stack exchange，提问作者user2483256