Thankyou for your help, I tried both of the formula's you suggested and both
return #REF! even when the spreadsheet is open. If you have any ideas as to
why this might be please let me know.



"David Biddulph" wrote:

You need to use the INDIRECT function.

Try =INDIRECT("["&A1&".xls]Pay_Details'!$C$6")
But if the content of A1 is not text but a number formatted as a date, you
may need
=INDIRECT("["&TEXT(A1,"dd-mmm-yy")&".xls]Pay_Details'!$C$6")
--
David Biddulph

"Me" wrote in message
...
Hi I have several excel files which automatically save as a date. I would
like to add a function to get data from a file defined in a cell.

I have tried
="[("&A1&").xls]"&"Pay_Details'!$C$6" but it displays #REF! not sure if
this
is becaus of the date format or some other problem.

eg. saved files "23-Sep-07", "30-Sep-07"

Main spreadsheet
A1= 23-Sep-07 B1- Datat from "23-Sep-07" file
A2= 30-Sep-07 B2- Datat from "23-Sep-07" file

Any help would be greatly appreciated