Data transfer from variable source

[srcasm83]

I need to transfer data from another spreadsheet. The title of the current spreadsheet is 01-10-09.xls and I need to get data from spreadsheet 01-09-09.xls (the day before) I already have the formula that gives me the day before in cell C40, so I need a formula that uses the date in cell C40 as the title of the spreadsheet that I am pulling data from. For example i would need data from [C40.xls]C8. I hope this makes sense. Thanks!

 

[texasalynn]

you can use the indirect function, but you will need to have the tab name also

 

[DonkeyOte]

Note however that XL's standard INDIRECT function would only work were the target file open... if it is not open you will need to use alternate approach.

 

[srcasm83]

The cell I'm transferring TO is tab "Grave daily sum" cell A8. This is where the formula is going. The filename of the file I'm transferring FROM is on tab "Report" cell C40 (This cell generates the day before). The cell I'm transferring FROM is on file: "(Report!C40).xls" tab: "Swing daily sum" cell: "C8" I have no idea how to write the formula.

 

[srcasm83]

I came close with ='[01-09-09.xls]Swing daily sum'!C8, but I need the date to change every day, so it doesn't work. And I want to be able to do it without having the target file open.