i am unable to use evaluate function to referring other file
1st file (opened) code exists in this file (named SFA)
Set m = ThisWorkbook.Sheets("sheet2")
2nd file (will search file name)
x.name is file name (which is opened)
Set sf = Workbooks(x.Name)
Set sf2 = sf.Sheets("Configurations")
below code isn't working if i made small changes it is workable with in file sheet to sheet but not working with referring file
Code: Select all
sf2.Cells(i, "j") = Evaluate("Sheet2!INDEX(a2:a" & p & ",SMALL(IF(""" & sf2.Cells(i, "n") & """=Sheet2!e2:e" & p & ",ROW(Sheet2!E2:E" & p & "),""""),COUNTIF('[x.name]Configurations'!n3:n" & i & ",""" & sf2.Cells(i, "n") & """)))")
Adeel