Thankyou
=INDIRECT("'["&TEXT(A1,"dd-mmm-yy")&".xls]Pay_Details'!c6")

works well

"Dave Peterson" wrote:

As long as the sending workbook is open, try:

=INDIRECT("'["&TEXT(A1,"dd-mmm-yy")&".xls]Pay_Details'!c6")

Since that expression is text, you don't even need $c$6.

If that doesn't work, check the name of that workbook and name of the worksheet.



Me wrote:

Thankyou for your help, I am unsure of how to use the =indirect() you
mentioned. Also I plan to have both spreadsheets open to get the data, after
which time I will not need to change it, I didn't realise about needing the
sending workbook to be open. Thanks I will rethink what I need to do now
maybe see if a Macro would work better.

"Dave Peterson" wrote:

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


--

Dave Peterson