Thanks
The formula that ended up working is
=INDIRECT("'["&TEXT(A1,"dd-mmm-yy")&".xls]Pay_Details'!c6")

"David Biddulph" wrote:

I now see that we've got one hiccup, which doesn't explain your original
#REF but might explain the new one. You (and I) have an odd apostrophe. If
the worksheet name doesn't include spaces it isn't needed, but if an
apostrophe is included in the formula there should be a pair. Let's change
=INDIRECT("["&A1&".xls]Pay_Details'!$C$6") to
=INDIRECT("["&A1&".xls]'Pay_Details'!$C$6") and
=INDIRECT("["&TEXT(A1,"dd-mmm-yy")&".xls]Pay_Details'!$C$6") to
=INDIRECT("["&TEXT(A1,"dd-mmm-yy")&".xls]'Pay_Details'!$C$6")

If we're still struggling, in a spare cell on the shhet where you're getting
the #REF, type an =sign, then go to the other sheet to the Pay_Details sheet
and click in cell C6, then accept the formula. Copy and paste from the
formula bar to the group here so that we can compare with the results of the
questions below, which should now be changed to
What do you get from ="["&A1&".xls]'Pay_Details'!$C$6" ? and
What do you get from ="["&TEXT(A1,"dd-mmm-yy")&".xls]'Pay_Details'!$C$6" ?
as well as What you go get from =A1 ?
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Loking back at your original formula ="[("&A1&").xls]"&"Pay_Details'!$C$6"
before we included the INDIRECT function, although it wouldn't have given
the cell context, I now realise that it shouldn't have given a #REF, as it
was returning a string, and the only reference in the string was to A1.
Was that formula copied and pasted from the formula bar to the newsgroup,
or was it retyped?

Let's go back a notch and see what text string we are passing to the
INDIRECT function.
What do you get from ="["&A1&".xls]Pay_Details'!$C$6" ?
What do you get from ="["&TEXT(A1,"dd-mmm-yy")&".xls]Pay_Details'!$C$6" ?

What you go get from =A1 ?
--
David Biddulph

"Me" wrote in message
...
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