| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DATE
This formula: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 which is in the May workbook looking at the April workbook will update from a closed April workbook just fine. When I open the May workbook with the April workbook closed, Excel asks if I want to update from an external source. I just click OK and it does it. I noticed that it even added the full path to my formula. It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries April 2008.xls]PARTS YE'!$E$31 Why then does: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 work just fine and the same formula that figures the date part (in this case: APRIL 2008) will not. Hence the need for a macro. Why can't I just replace [Journal Entries April 2008.xls] with something like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel ask me to update when I open the workbook just like it does with the 1st formula above? I have tried this but the structure of the formula is different. The orginal formula uses brackets: [ ], while the one just above does not. Anyway this is what I am trying to do so it is automated. If someone could figure out how to make this formula work then no macro would be needed. If it is really not possible I would like to know why so I don't end up trying this again in the future. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DATE
Look in HELP for the INDIRECT() function
-- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DATE
Not good: will not work with a closed workbook.
I know Harlan Grove developed something to cure this but I'll have to find it first. In the meantime, you try and Google too :-) -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | Look in HELP for the INDIRECT() function | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Joe" wrote in message ... || || This formula: || || ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 || || which is in the May workbook looking at the April workbook will update from || a closed April workbook just fine. When I open the May workbook with the || April workbook closed, Excel asks if I want to update from an external || source. I just click OK and it does it. I noticed that it even added the full || path to my formula. || || It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 || To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries || April 2008.xls]PARTS YE'!$E$31 || || Why then does: || || ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 || || work just fine and the same formula that figures the date part (in this || case: APRIL 2008) will not. Hence the need for a macro. || || Why can't I just replace [Journal Entries April 2008.xls] with something || like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM || YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel || ask me to update when I open the workbook just like it does with the 1st || formula above? || || I have tried this but the structure of the formula is different. The orginal || formula uses brackets: [ ], while the one just above does not. || || Anyway this is what I am trying to do so it is automated. || || If someone could figure out how to make this formula work then no macro || would be needed. If it is really not possible I would like to know why so I || don't end up trying this again in the future. || || Thank you. | | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
Thanks but since the INDIRECT() funtion requires the other workbook to be
open it is pretty useless for automating the process. "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
You need VBA for this, there is nothing built in.
Download and install the free add-in Morefunc.xll from: http://xcell05.free.fr/english/ then there is a finction called INDIRECT.EXT -- Regards, Peo Sjoblom "Joe" wrote in message ... Thanks but since the INDIRECT() funtion requires the other workbook to be open it is pretty useless for automating the process. "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
I downloaded the morefunc and used the INDIRECT.EXT instead of INDIRECT in
this formula: =INDIRECT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") =INDIRECT.EXT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") And it worked just like INDIRECT. It will NOT retreive the data if the other book is closed but works ok if the other book is open. "Peo Sjoblom" wrote: You need VBA for this, there is nothing built in. Download and install the free add-in Morefunc.xll from: http://xcell05.free.fr/english/ then there is a finction called INDIRECT.EXT -- Regards, Peo Sjoblom "Joe" wrote in message ... Thanks but since the INDIRECT() funtion requires the other workbook to be open it is pretty useless for automating the process. "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
Works fine for me
-- Regards, Peo Sjoblom "Joe" wrote in message ... I downloaded the morefunc and used the INDIRECT.EXT instead of INDIRECT in this formula: =INDIRECT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") =INDIRECT.EXT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") And it worked just like INDIRECT. It will NOT retreive the data if the other book is closed but works ok if the other book is open. "Peo Sjoblom" wrote: You need VBA for this, there is nothing built in. Download and install the free add-in Morefunc.xll from: http://xcell05.free.fr/english/ then there is a finction called INDIRECT.EXT -- Regards, Peo Sjoblom "Joe" wrote in message ... Thanks but since the INDIRECT() funtion requires the other workbook to be open it is pretty useless for automating the process. "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
You can accomplish this without using code if you have, or you're willing to
have, your WB names in a list, say down Column A, and then use formulas in Column B, referring to the names in Column A. Post back if you would like to go this route. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Joe" wrote in message ... Thanks but since the INDIRECT() funtion requires the other workbook to be open it is pretty useless for automating the process. "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
That sounds interesting. How would it work?
"Ragdyer" wrote: You can accomplish this without using code if you have, or you're willing to have, your WB names in a list, say down Column A, and then use formulas in Column B, referring to the names in Column A. Post back if you would like to go this route. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Joe" wrote in message ... Thanks but since the INDIRECT() funtion requires the other workbook to be open it is pretty useless for automating the process. "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
You create a "Text" formula, which has the ability to add the data in a cell
as a "legal" XL reference. You then convert the "Text" formula to a 'working' XL formula which can then return values from either open or closed WBs. You can even create these Text formulas ahead of time, before the actual future WBs exist, where you convert them to real formulas *after* constructing those future WBs. To make things a little easier, post the *full* path to your Journal Entries April 2008 workbook. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Joe" wrote in message ... That sounds interesting. How would it work? "Ragdyer" wrote: You can accomplish this without using code if you have, or you're willing to have, your WB names in a list, say down Column A, and then use formulas in Column B, referring to the names in Column A. Post back if you would like to go this route. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Joe" wrote in message ... Thanks but since the INDIRECT() funtion requires the other workbook to be open it is pretty useless for automating the process. "Niek Otten" wrote: Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Joe" wrote in message ... | | This formula: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | which is in the May workbook looking at the April workbook will update from | a closed April workbook just fine. When I open the May workbook with the | April workbook closed, Excel asks if I want to update from an external | source. I just click OK and it does it. I noticed that it even added the full | path to my formula. | | It went from: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | To: ='C:\Documents and Settings\FULL PATH.................\[Journal Entries | April 2008.xls]PARTS YE'!$E$31 | | Why then does: | | ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 | | work just fine and the same formula that figures the date part (in this | case: APRIL 2008) will not. Hence the need for a macro. | | Why can't I just replace [Journal Entries April 2008.xls] with something | like: ="Journal Entries "&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"MMMM | YYYY") &".xls" (which returns Journal Entries April 2008.xls) And have Excel | ask me to update when I open the workbook just like it does with the 1st | formula above? | | I have tried this but the structure of the formula is different. The orginal | formula uses brackets: [ ], while the one just above does not. | | Anyway this is what I am trying to do so it is automated. | | If someone could figure out how to make this formula work then no macro | would be needed. If it is really not possible I would like to know why so I | don't end up trying this again in the future. | | Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Are Error-Checking rules (in Excel Options) workbook-specific? | Excel Discussion (Misc queries) | |||
| Retrieve text from filter data in workbook 1 and place values in 2 | Excel Discussion (Misc queries) | |||
| Retrieve Excel Workbook | Excel Worksheet Functions | |||
| How do I retrieve & view multiple matches from another workbook? | Excel Worksheet Functions | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Hybrid Mode
