External Workbook Link with Dynamic Name

I'm trying have a cell pull data from a external workbook using a formula with the filename being the date the report was generated.

='C:\Reports\[2009-01-12.xls]Sheet1'!B9

My problem is that i'm trying have the filepath automatically updated each day. so tomorrow it'll be

='C:\Reports\[2009-01-13.xls]Sheet1'!B9

I have tried using year(), month(), day() functions and concatenating it as a string then tried using the indirect() function, but no luck, i keep getting a #REF. Has anybody here had any success with this, or know how to get it work without using macros?

Thanks,
Adam

Hi Adam

INDIRECT will only work with an external workbook if that workbook is open. An alternative might be to download the morefunc add-in (try Googling 'morefunc') which will give you access to the INDIRECT.EXT which will work with closed workbooks. Failing that you would need to go into Edit>Links and update the links manually (eg if you have a lot to do). You could automate this using a macro if you wished.

Richard