|
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. |
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. |
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. | | |
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. |
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. |
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. |
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. |
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. |
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. |
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
I am using the formula:
=INDIRECT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") instead of: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 It works fine when the April workbook is also open but not if the April workbook is closed. I downloaded and tried INDIRECT.EXT also and it did the same thing. What formula would you use to follow your suggestion? "Ragdyer" wrote: 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. |
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
I *repeat*, 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 ... I am using the formula: =INDIRECT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") instead of: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 It works fine when the April workbook is also open but not if the April workbook is closed. I downloaded and tried INDIRECT.EXT also and it did the same thing. What formula would you use to follow your suggestion? "Ragdyer" wrote: 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. |
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
I tried that after I saw that Excel does it automatically when it asks you to
update links when you open the workbook with external source data. It did not make any difference. Maybe if you give me an example of what your formula would look like I could see what I am doing wrong. "Ragdyer" wrote: I *repeat*, 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 ... I am using the formula: =INDIRECT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") instead of: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 It works fine when the April workbook is also open but not if the April workbook is closed. I downloaded and tried INDIRECT.EXT also and it did the same thing. What formula would you use to follow your suggestion? "Ragdyer" wrote: 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. |
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. |
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DA
The reason I'm asking for the full path, is since we're going to start off
with a text formula, XL won't fill it in automatically. You can open both the Journal Entries April 2008 WB, and the WB where you want to collect this data, let's call it "Main", and create a simple link between any 2 cells. Then, when you close the Journal Entries April 2008 you'll see the full path to it. Let's start with an example of how to create a type of formula that can use data in cells, and include it in XL formulas to reference links in closed WBs. Start a new WB and select A2 to A13. Format that selection to Text, then enter: January 2008 February 2008 March 2008 .... etc. I'm assuming this is how you're spelling the date portion of your WB names. Using the example in your OP, in an out of the way location, say in J2, enter this formula: ="='C:\Documents and Settings\Full Path\[Journal Entries "&A2&".xls]PARTS YE'!$E$31" You'll need to change "Full Path" to the actual path to your WBs. You'll notice that what you see in J2, and what you see in the formula bar is different, where J2 will display the date data from A2, and the formula bar displays "&A2&". Now, select J2 and drag down to copy to J13, to create 12 formulas for an entire year, Jan. to Dec. While these 12 cells are *still* selected from the copy down, right click in the selection and choose "Copy". Right click in B2 and choose "Paste Special", click on "Values", then <OK. You now have Text formulas in Column B, where the data in Column A has been incorporated into the formulas. If all those 12 WBs exist, and you've properly entered the correct path in place of "Full Path", you're ready to convert those Text formulas to legal, working, XL formulas. Select B2 to B13, and from the Menu Bar, <Data <Text To Columns <Delimited <Finish And you should have the data from E31 displayed for all your WBs. You have 12 valid links to those WBs. If a WB doesn't exist, you'll get a #Ref! error. If you later create a WB with that exact matching name, and save it, that #Ref! error will change to display the data in E31 of that particular WB. You can save the formulas in Column J, so that you will have the proper syntax for any future revisions you may need/want. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Joe" wrote in message news:739E2A78-BB3A-4001-B34A-FE6D3064D4C3y create a @microsoft.com... I tried that after I saw that Excel does it automatically when it asks you to update links when you open the workbook with external source data. It did not make any difference. Maybe if you give me an example of what your formula would look like I could see what I am doing wrong. "Ragdyer" wrote: I *repeat*, 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 ... I am using the formula: =INDIRECT("'[Journal Entries "&TEXT(DATE(YEAR($B$1),MONTH($B$1)-1,1),"MMMM YYYY")&".xls]PARTS YE'!E$31") instead of: ='[Journal Entries April 2008.xls]PARTS YE'!$E$31 It works fine when the April workbook is also open but not if the April workbook is closed. I downloaded and tried INDIRECT.EXT also and it did the same thing. What formula would you use to follow your suggestion? "Ragdyer" wrote: 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. |
| All times are GMT +1. The time now is 02:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com