Changing Formula Locations

[Guest]

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

 

[Dave Peterson]

The worksheet function that you'd want to use is =indirect().

But =indirect() doesn't work with closed workbooks.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

You may want to use a macro that would build the formulas to retrieve the values
from a closed workbook (on a hidden worksheet), then retrieve from there?

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