I have the following issue, I try to make a formula to refer to an external excel file based on the date. When try format the date to ddmmyy and use it in this formula I get a #ref error.
=IFERROR(COUNTIFS('R:hal 6nicarmLijstenPerDagExcel[230924.xlsx]Data'!$V$2:$V$999;
">=01/01/1970 22:00:00";'R:hal 6nicarmLijstenPerDagExcel[230924.xlsx]Data'!$A$2:$A$999;"LA")
+COUNTIFS('R:hal 6nicarmLijstenPerDagExcel[240924.xlsx]Data'!$V$2:$V$999;
"<=01/01/1970 06:00:00";'R:hal 6nicarmLijstenPerDagExcel[240924.xlsx]Data'!$A$2:$A$999;"LA");"")
This formula works, but when i try to use indirect to make the path a variable i get the error.
Edit:
The countifs i got working by opening the external workbooks with 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 6nicarmLijstenPerDagExcel180924.xlsx")
Set wkb2 = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel190924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel200924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel210924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel220924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel230924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel240924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel250924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel260924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel270924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel280924.xlsx")
wkb2.Close (False)
Set wkb2 = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel290924.xlsx")
wkb.Close (False)
Set wkb = Workbooks.Open("R:hal 6nicarmLijstenPerDagExcel300924.xlsx")
wkb2.Close (False)
wkb.Close (False)
Application.ScreenUpdating = True
Application.Cursor = xlDefault
End Sub
It is very basic I should use a loop to go through the workbooks but I used this as a placeholder.
2