SUMIF data with variable file name possible?

[Mifunego]

My research on this topic here isn't giving me any good leads but... I thought I'd ask the wise brain-pool anyway.

I have a report that I am currently running based on raw data downloaded daily. I need to be able to compare data from any download date to any other download date. This is simple with one database, but as you can imagine, that file is getting HUGE.

What I'd like to do is have my report be able to compare data from any two files based on user-entered file name. For example, the user would enter any two dates in the proper cells on the inquiry/report page, and the comparison would run for those two files only. The file names are simply the download date, 0522209.xls for example.

Is there some proper formula method to get that to work? More simply, can there be a variable file name within the lookup formula?
Example:
=sumif([052609.xls]Sheet1!$A:$A,criteria,[sum range])
but I want it to do something like:
=sumif([filename entered in cell A1.xls]Sheet1!$A:$A,criteria,[sum range])

Sorry for the meandering explanation, my brain is Excel-beat at this point. I know this is probably better suited to a proper Access database, but my knowledge of Access is limited, and my knowledge of VBA is virtually nonexistent. Any ideas, gurus?

*EDIT* If it matters, I'm using Excel 2007, and its many lovely functions. SUMIFS ftw!

 

[nkiouari]

<table border="0" width="90%" height="107"><tbody><tr><td valign="top" width="56%">[FONT=Arial, Helvetica, sans-serif]Create a Reference To a Different Workbook[/FONT]

An INDIRECT formula can refer to cells in other workbooks, but will return a #REF! error if that workbook is closed. In this example, you'll create an INDIRECT formula, using references to a file name, sheet name and cell name.

</td> <td colspan="2" width="44%">
</td> </tr> </tbody></table> <table border="0" width="90%" height="167"> <tbody><tr> <td valign="top" width="56%">

1. In an open file named TestFile.xls, on a worksheet named Test Data, enter numbers in cells A1:A10
2. On a sheet in a different workbook, in cell A2, enter the file name:
   Test File.xls
3. In cell A3, enter the sheet name: Test Data
4. In cell A4, type a cell name from the range of numbers, e.g.: A7
5. To see the syntax that you'll need to use in your formula, select cell A7, and type an equal sign: =
6. Switch to Test File.xls, and click on cell A7
7. In the formula bar, you'll see the reference that was created:
   ='[Test File.xls]Test Data'!$A$7
8. You'll create an INDIRECT formula that uses the same syntax, adding the single quote marks, square brackets and apostrophe.
9. In cell A6, type the following formula:
   =INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)
10. The first part of the string is a single quote and square bracket, within a set of double quotes:
    " ' [ " (spaces were added for clarity)
11. After the reference to A2, which contains the file name, is a square bracket, within a set of double quotes:
    " ] " (spaces were added for clarity)
12. After the reference to A3, which contains the sheet name, is a single quote and apostrophe, within a set of double quotes:
    " ' ! " (spaces were added for clarity)
13. The string ends with a reference to cell A4, which contains the cell address.
14. Single quotes are included in the string to prevent errors if the sheet name contains space characters.
15. Press the Enter key, and the formula returns the number in cell A7 on the Test Data worksheet, in the Test File.xls workbook.
16. Change the values in cells A2:A5, and the result in A6 will change.
17. You can delete the sample link in cell A5.

Note1: If A2, A3 or A4 is empty, the formula will return an error. To prevent this, you can add an IF function


=IF(OR(A2="",A3="",A4=""),"",INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4))


Hoping You've been satisfied!
I got this trick from another website

​

</td></tr></tbody></table>