First, you'd want to format that date value in A1:

...&text(a1,"dd-mmm-yy")&...

And you'd want to use =indirect() in your formula.

But the bad news is your formula will work as long as that sending workbook is
open. As soon as you close that workbook (and excel recalcs), you'll get an
error.

If you're going to have that sending workbook closed, then Laurent Longre has an
addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Me wrote:

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

--

Dave Peterson