Excel help - referencing multiple other workbooks

[alx]

Was wondering if anyone can help.

I'm basically trying to collate data from various daily report workbooks into one master spreadsheet. The daily report workbooks have the same format, but are named differently depending on the date (their naming convention is date based, i.e. 01.01.14.xls, 02.01.14.xls etc).

I know how to reference cells etc from an external workbook/spreadsheet, what I'm having trouble with is how to reference the same cells in workbooks with different names, without having to type the name of workbook manually each time.

Seeing as the the workbooks are named logically by date (as shown above), is there a way I can link a column of dates (on the format dd.mm.yy) to the workbook name in the formula, ie:

Instead of having this formula and having to edit it for each day, ie:
='C:\Desktop\[01.01.14.xls]Sheet1'!C1

Could I have something which changes the workbook name depending on the date/reference in another cell? I.e. the part in square brackets would change depending on the date reference in a different cell?

Hope that makes sense! Haven't had much luck with google.

 

[Amraam]

Providing they're all the same structure, could you use the =TODAY() function?

Say have a spare cell (H10 for arguments sake) as =TODAY(), then use your formula to point to ='C:\Desktop\[($H$10).xls]Sheet1'!C1

I'm just guessing here, so what I've suggested is likely wrong, but it's a start for someone with more knowledge to correct.

 

[Grrrrr]

It's not possible with Excel's built in functions I'm afraid. INDIRECT will do it but that will stop working as soon as you close the external workbook.

VBA would be the only other way.