I produce a report every day, and it is saved as the current date.
I want a central spreadsheet that can extract data from these reports
dependant on cell B1 which is a date I can enter manually.

I want to get the data from say B2 on the sheet S:\New Productivity
Sheets\[03.03.06.xls]Consolidated'!$B$2

Is there any way at all that I can alter which spread sheet this formula
looks at? like if I enter 02/03/06 into B1 it change the information to come
from
S:\New Productivity Sheets\[02.03.06.xls]Consolidated'!$B$2
and if I change it to 28/02/06
S:\New Productivity Sheets\[28.02.06.xls]Consolidated'!$B$2

Thanks in advance