Should that read:
SUMIFS([200902_UPS.xls]UPS_CSV_EXPORT!$G:$G, A117,
[200902_UPS.xls]UPS_CSV_EXPORT!$F:$F)
As the syntax is =SUMIF( range, criteria, sum-range)
My answer mirros Luke's but has a slightly different approach
In A1 I have ="["&YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&"_UPS.xl s]"
This generates 200903
In A2 I have: UPS_CSV_EXPORT!
In A3 the formula =A1&A2&"$G:$G" generates
[200903_UPS.xls]UPS_CSV_EXPORT!$G:$G
In A4 the formula =A1&A2&"$F:$F" generates
[200903_UPS.xls]UPS_CSV_EXPORT!$F:$F
And finally in A5 I use =SUMIF(INDIRECT(A3),A117,INDIRECT(A4))
to get the right answer
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Tigerxxx" wrote in message
...
Hello,
What formula can I use to make the file name variable?
Ex: If my formula reads as follows:
SUMIFS([200902_UPS.xls]UPS_CSV_EXPORT!$G:$G,[200902_UPS.xls]UPS_CSV_EXPORT!$F:$F,A117)
For the month of February, the file name is 200902_UPS.xls.
I want to make the 200902 portion variable by referencing to a cell which
provides the month number so that in March, the name would be 200903.
Thank you