getting values from a filename as a date in a cell

[Me]

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]

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.

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

 

[David Biddulph]

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")

 

[Me]

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.

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

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

 

[Me]

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.

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.

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]

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.

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.

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.

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

 

[David Biddulph]

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

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.

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

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

 

[David Biddulph]

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

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

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.

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

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

 

[Me]

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

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

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

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.

:

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

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

 

[Me]

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

works well

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.

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.

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

 

[David Biddulph]

Oh, I oughtn't to try this sort of thing at the end of a long week!

The apostrophes should surround the filename as well as sheetname.
=INDIRECT("'["&A1&".xls]Pay_Details'!$C$6") or
=INDIRECT("'["&TEXT(A1,"dd-mmm-yy")&".xls]Pay_Details'!$C$6")
--
David Biddulph

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

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

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.

:

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

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

 

[Teddy-B]

The site is suspended. Is there another site to get the morefunc addin? Could
you post a hyperlink?
Many Thanks:

Teddy B

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.

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

 

[David Biddulph]

Try http://xcell05.free.fr/english/
--
David Biddulph

The site is suspended. Is there another site to get the morefunc addin?
Could
you post a hyperlink?
Many Thanks:

Teddy B

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.

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