Daily Spreadsheet References

I have a spreadsheet that needs to reference another spreadsheet to obtain a daily target figure. Unfortunately the way the system is set up at work, each day of the year has it's own spreadsheet in it's own folder, and the figure I need needs to be updated each day from the corresponding spreadsheet.

At the moment I simply have 366 (a spare unused one for leap years) different formulas to compare dates and return the figure from todays date. The downside here is that it takes excel 50 seconds to open the spreadsheet because of this, so I assume it's checking all of these figures in all those spreadsheets instead of just the one that's true.

so I have =IF(E2=AF2,spreadsheet address,0) Where E2 is todays date and AF2 is a date from a list.

What I'd like is a method to do something similar but with one or two formulas that will simply update the address of the file I need the figure from based on what date it is so that it will only look at one spreadsheet when it opens instead of all 365.

I tried the following:
IF(E$2=AF302,('C:spreadsheets('H:[Punch 2 Spreadsheet.xlsb]Punch 2 Metrics'!Z$28)[UVS Cell WEB.xlsx]Punch 2'!B$3)/1000,#N/A)

Where the section in bold replaces the part of the address with the date folders (20091091) for example, and instead has a cell reference which is formatted to replace this section and updates automatically each day.

It does not work obviosuly and I wanted to know I'm just not formatting the formula correctly or if this idea is a dead end.