Sumproduct can work with closed files but indirect doesn't so
How about this idea of a drop down list with the file names and
worksheet_change macro
right click sheet tabview codeinsert this. now when 2010 is selected from
the drop down list the 4 digit year code is replaced with 2010

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < Range("j16").Address Then Exit Sub
Set mr = Range("j15")
fxls = InStr(mr.Formula, ".xls")
'MsgBox Mid(mr.Formula, fxls - 4, 4)
mr.Replace Mid(mr.Formula, fxls - 4, 4), Target
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chechu" wrote in message
...
I have the following macro:

Sub tracking()

engid = "'[Export January.xls]Export_January'!$aq$2:$aq$43735"
rev = "'[Export January.xls]Export_January'!$ag$2:$ag$43735"

engid2 = Sheets("actual").Range("H4").Value

If Not IsNumeric(engid2) Then
engid2 = """" & engid2 & """"
End If
pct = Application.Evaluate("SUMPRODUCT(--(" & engid & "=" _
& engid2 & "),--(" & rev & "))")

ThisWorkbook.Worksheets("actual").Range("AL12").Se lect
Range("al12").Value = pct

End Sub

It works OK but, source file is hard coded. What I want to do is to
allow the user to select the source file, and probably open the file
(sumproduct in VBA works with closed files??? I don�t think so). I
know I should include something like:

Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
..AllowMultiSelect = False
If .Show = -1 Then
Set sourcebook = Workbooks.Open(Filename:=fd.SelectedItems(1))
With sourcebook�.???????

��..But how to assign to a variable the file name for the selected
file, and also, how to modify the sumproduct syntax to use it???

Thanks,
Cecilia