Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable File Name
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable File Name
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.) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tigerxxx" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable File Name
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable File Name
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.) Tigerxxx wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable File Name
Thank you Luke, Bernard, Dave...the solutions are pretty interesting. I will
try them out! "Dave Peterson" wrote: 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.) Tigerxxx wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calling a value from another file using a variable in the file nam | Excel Worksheet Functions | |||
calling a value from another file using a variable in the file nam | Excel Discussion (Misc queries) | |||
Referring to a variable file name | Excel Discussion (Misc queries) | |||
Variable File Name | Excel Worksheet Functions | |||
Inport from a variable file name | Excel Discussion (Misc queries) |