| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting values from a filename as a date in a cell
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
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 "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
The site is suspended. Is there another site to get the morefunc addin? Could
you post a hyperlink? Many Thanks: Teddy B "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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
getting values from a filename as a date in a cell
Try http://xcell05.free.fr/english/
-- David Biddulph "Teddy-B" wrote in message ... The site is suspended. Is there another site to get the morefunc addin? Could you post a hyperlink? Many Thanks: Teddy B "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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding date for filename | Excel Discussion (Misc queries) | |||
| Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
| set filename to <filename-date on open | Excel Worksheet Functions | |||
| How to automate footers (filename/date) for all new spreadsheets? | Excel Worksheet Functions | |||
| Using TODAY() to generate a filename from where values are VLOOKUPed | Links and Linking in Excel | |||
Linear Mode
