Variable File Name

[Tigerxxx]

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

 

[Luke M]

I do not have XL 2007, so this is untested, but it should work:

=SUMIFS(INDIRECT("'["&A1&"_UPS.xls]UPS_CSV_EXPORT'!$G:$G"),INDIRECT("["&A1&"_UPS.xls]UPS_CSV_EXPORT'!$F:$F"),A117)

Where A1 contains you month values such as 200902 (I'd recommend including
the year so you don't have to worry about leading zeros.)

 

[Bernard Liengme]

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.xls]"
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

 

[Dave Peterson]

If that sending file is closed, then =indirect() won't work. =indirect()
requires that the sending workbook be open.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

And to make matters worse, =sumif() in xl2003 and below won't work with
=indirect(). I don't know about xl2007 and I don't know about xl2007's
=sumifs().

But there are alternatives if it doesn't. You can use =sumproduct(). (But that
can't use entire columns until xl2007.)

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

 

[Tigerxxx]

Thank you Luke, Bernard, Dave...the solutions are pretty interesting. I will
try them out!

If that sending file is closed, then =indirect() won't work. =indirect()
requires that the sending workbook be open.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

And to make matters worse, =sumif() in xl2003 and below won't work with
=indirect(). I don't know about xl2007 and I don't know about xl2007's
=sumifs().

But there are alternatives if it doesn't. You can use =sumproduct(). (But that
can't use entire columns until xl2007.)

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