| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Baffling problem with links
1. Working in a network environment.
2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#2
|
|||
|
|||
|
Hi
this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#3
|
|||
|
|||
|
Thanks, Frank. I forgot to mention that when I open all the source workbooks
the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#4
|
|||
|
|||
|
Hi
this is just the usage of your syntax. Your kind of cell reference is just not correct for closed workbooks -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thanks, Frank. I forgot to mention that when I open all the source workbooks the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#5
|
|||
|
|||
|
Frank:
Then you're saying it is not possible to do what I want using the structure SUM(INDEX(...):INDEX(...)). How unjust!!! The alternative would be hiding twelve columns (one for each month) in the destination workbook each of which picks up only one cell in the source workbook and my INDEX function would operate in the thirteenth column to SUM the correct number of columns depending on which months I want. Would working with ARRAY help here? Thanks, Stephen "Frank Kabel" wrote: Hi this is just the usage of your syntax. Your kind of cell reference is just not correct for closed workbooks -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thanks, Frank. I forgot to mention that when I open all the source workbooks the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#6
|
|||
|
|||
|
Hi Stephen
it would help if you could post the complete formula. There's probably a different way -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Frank: Then you're saying it is not possible to do what I want using the structure SUM(INDEX(...):INDEX(...)). How unjust!!! The alternative would be hiding twelve columns (one for each month) in the destination workbook each of which picks up only one cell in the source workbook and my INDEX function would operate in the thirteenth column to SUM the correct number of columns depending on which months I want. Would working with ARRAY help here? Thanks, Stephen "Frank Kabel" wrote: Hi this is just the usage of your syntax. Your kind of cell reference is just not correct for closed workbooks -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thanks, Frank. I forgot to mention that when I open all the source workbooks the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#7
|
|||
|
|||
|
Thanks for your time and help, Frank.
Here's the formula: =SUM(INDEX('S:\_Finance_and_Administration_Corpora te\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and _Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S:\_Financ e_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Finance_and _Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and _Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\_Finance _and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1)) Stephen "Frank Kabel" wrote: Hi Stephen it would help if you could post the complete formula. There's probably a different way -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Frank: Then you're saying it is not possible to do what I want using the structure SUM(INDEX(...):INDEX(...)). How unjust!!! The alternative would be hiding twelve columns (one for each month) in the destination workbook each of which picks up only one cell in the source workbook and my INDEX function would operate in the thirteenth column to SUM the correct number of columns depending on which months I want. Would working with ARRAY help here? Thanks, Stephen "Frank Kabel" wrote: Hi this is just the usage of your syntax. Your kind of cell reference is just not correct for closed workbooks -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thanks, Frank. I forgot to mention that when I open all the source workbooks the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#8
|
|||
|
|||
|
The same formula simplified is:
=SUM(INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.xls]CombinedPL!$B$6:$B$500,0),MATCH("JAN",[Vancouver.xls]CombinedPL!$B$6:$O$6,0),1):INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.xls]CombinedPL!$B$6:$B$500,0),MATCH($B$5,[Vancouver.xls]CombinedPL!$B$6:$O$6,0),1)) where $B$5 = "NOV" to identify the current month (specifies the column) and $B$9 = "Total Revenue" (specifies the row) Thanks, Stephen "Stephen POWELL" wrote: Thanks for your time and help, Frank. Here's the formula: =SUM(INDEX('S:\_Finance_and_Administration_Corpora te\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and _Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S:\_Financ e_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Finance_and _Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Finance_and _Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\_Finance _and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1)) Stephen "Frank Kabel" wrote: Hi Stephen it would help if you could post the complete formula. There's probably a different way -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Frank: Then you're saying it is not possible to do what I want using the structure SUM(INDEX(...):INDEX(...)). How unjust!!! The alternative would be hiding twelve columns (one for each month) in the destination workbook each of which picks up only one cell in the source workbook and my INDEX function would operate in the thirteenth column to SUM the correct number of columns depending on which months I want. Would working with ARRAY help here? Thanks, Stephen "Frank Kabel" wrote: Hi this is just the usage of your syntax. Your kind of cell reference is just not correct for closed workbooks -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thanks, Frank. I forgot to mention that when I open all the source workbooks the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#9
|
|||
|
|||
|
Hi
and you want to sum this specified row from JAN to NOV (if this is the current month)?. If yes: would it be feasible for you to change the heading row to a real date (e.g. 01-Nov-2004) and just format it with the custom format MMM If yes you may try then the following formula (not tested though): =SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(MONTH([Vancouv er.xls]CombinedPL!$C$6:$O$6)<=$B$5)*([Vancouver.xls]CombinedPL!$C$7:$O$ 500)) where B5 contains 11 in this example -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... The same formula simplified is: =SUM(INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.x ls]CombinedPL!$B$6:$B$500,0),MATCH("JAN",[Vancouver.xls]CombinedPL!$B$6 :$O$6,0),1):INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vanc ouver.xls]CombinedPL!$B$6:$B$500,0),MATCH($B$5,[Vancouver.xls]CombinedP L!$B$6:$O$6,0),1)) where $B$5 = "NOV" to identify the current month (specifies the column) and $B$9 = "Total Revenue" (specifies the row) Thanks, Stephen "Stephen POWELL" wrote: Thanks for your time and help, Frank. Here's the formula: =SUM(INDEX('S:\_Finance_and_Administration_Corpora te\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin ance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S: \_Finance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Fin ance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin ance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\ _Finance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1)) Stephen "Frank Kabel" wrote: Hi Stephen it would help if you could post the complete formula. There's probably a different way -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Frank: Then you're saying it is not possible to do what I want using the structure SUM(INDEX(...):INDEX(...)). How unjust!!! The alternative would be hiding twelve columns (one for each month) in the destination workbook each of which picks up only one cell in the source workbook and my INDEX function would operate in the thirteenth column to SUM the correct number of columns depending on which months I want. Would working with ARRAY help here? Thanks, Stephen "Frank Kabel" wrote: Hi this is just the usage of your syntax. Your kind of cell reference is just not correct for closed workbooks -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thanks, Frank. I forgot to mention that when I open all the source workbooks the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#10
|
|||
|
|||
|
Thank you, Frank. You're very resourceful.
Unfortunately, the models which are the source workbooks use text and not numerics for the dates (too much work to change every model). However, I could probably: a) modify your approach to identify DEC and subtract this from the twelve month total; or b) use the existing INDEX approach to do the same thing (12 months minus DEC). This would be a fix for now and I could re-consider the design of the source workbooks for next year's version. Thanks again. Stephen "Frank Kabel" wrote: Hi and you want to sum this specified row from JAN to NOV (if this is the current month)?. If yes: would it be feasible for you to change the heading row to a real date (e.g. 01-Nov-2004) and just format it with the custom format MMM If yes you may try then the following formula (not tested though): =SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(MONTH([Vancouv er.xls]CombinedPL!$C$6:$O$6)<=$B$5)*([Vancouver.xls]CombinedPL!$C$7:$O$ 500)) where B5 contains 11 in this example -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... The same formula simplified is: =SUM(INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vancouver.x ls]CombinedPL!$B$6:$B$500,0),MATCH("JAN",[Vancouver.xls]CombinedPL!$B$6 :$O$6,0),1):INDEX([Vancouver.xls]CombinedPL!$B$6:$O$500,MATCH(B$9,[Vanc ouver.xls]CombinedPL!$B$6:$B$500,0),MATCH($B$5,[Vancouver.xls]CombinedP L!$B$6:$O$6,0),1)) where $B$5 = "NOV" to identify the current month (specifies the column) and $B$9 = "Total Revenue" (specifies the row) Thanks, Stephen "Stephen POWELL" wrote: Thanks for your time and help, Frank. Here's the formula: =SUM(INDEX('S:\_Finance_and_Administration_Corpora te\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin ance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH("JAN",'S: \_Finance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1):INDEX('S:\_Fin ance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$500,MATCH(B$9,'S:\_Fin ance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$B$500,0),MATCH($B$5,'S:\ _Finance_and_Administration_Corporate\Forecast - New 2004 Models\Models\[Vancouver.xls]CombinedPL'!$B$6:$O$6,0),1)) Stephen "Frank Kabel" wrote: Hi Stephen it would help if you could post the complete formula. There's probably a different way -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Frank: Then you're saying it is not possible to do what I want using the structure SUM(INDEX(...):INDEX(...)). How unjust!!! The alternative would be hiding twelve columns (one for each month) in the destination workbook each of which picks up only one cell in the source workbook and my INDEX function would operate in the thirteenth column to SUM the correct number of columns depending on which months I want. Would working with ARRAY help here? Thanks, Stephen "Frank Kabel" wrote: Hi this is just the usage of your syntax. Your kind of cell reference is just not correct for closed workbooks -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thanks, Frank. I forgot to mention that when I open all the source workbooks the linking formulas in the destination workbook produce the correct results. You may be on to something so I went back and re-performed the EVALUATE FORMULA and the way I first presented it is accurate. I'm still baffled. "Frank Kabel" wrote: Hi this is not a valid reference you have created. The valid reference would look like: SUM('path\[filename]\worksheet'!$C$27:$J$27) You may post the complete formula -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... 1. Working in a network environment. 2. My dependent workbook in one subdirectory links to several source workbooks all in one other directory (this has never been a problem before). 3. All such linking formulas show "#REF!". But everything worked fine last week when I created the dependent workbook with its links. 4. Formula in dependent workbook is: SUM(INDEX(...BLAH...BLAH):INDEX(...BLAH...BLAH)) 5. I have used TOOLS/FORMULA AUDITING/EVALUATE FORMULA to confirm that the INDEX arguments within the SUM formula each evaluate to the proper cells in the source workbooks. In other words, I proceeded through EVALUATE FORMULA until I got SUM('path\[filename]\worksheet'!$C$27:'path\[filename]\worksheet'!$J$27 ) and the next EVALUATE step produced the "REF!". Any suggestions? Many thanks, Stephen Powell |
|
#11
|
|||
|
|||
|
Hi
with text values it would get complicated. To be honest: Better change your model once but to deal with too complicated formulas :-). Saying that you may try the following (though not tested): =SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou v er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6 :$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500)) -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thank you, Frank. You're very resourceful. Unfortunately, the models which are the source workbooks use text and not numerics for the dates (too much work to change every model). However, I could probably: a) modify your approach to identify DEC and subtract this from the twelve month total; or b) use the existing INDEX approach to do the same thing (12 months minus DEC). This would be a fix for now and I could re-consider the design of the source workbooks for next year's version. Thanks again. Stephen "Frank Kabel" wrote: |
|
#12
|
|||
|
|||
|
Frank:
That's a very creative idea. I will give that a try. Thanks again, Stephen "Frank Kabel" wrote: Hi with text values it would get complicated. To be honest: Better change your model once but to deal with too complicated formulas :-). Saying that you may try the following (though not tested): =SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou v er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6 :$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500)) -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thank you, Frank. You're very resourceful. Unfortunately, the models which are the source workbooks use text and not numerics for the dates (too much work to change every model). However, I could probably: a) modify your approach to identify DEC and subtract this from the twelve month total; or b) use the existing INDEX approach to do the same thing (12 months minus DEC). This would be a fix for now and I could re-consider the design of the source workbooks for next year's version. Thanks again. Stephen "Frank Kabel" wrote: |
|
#13
|
|||
|
|||
|
Sorry, Frank. I can't get your suggestion to work. The succession of FALSE
values evaluate to zero and are multiplied together to produce zero. I even changed your CombinedPL!$C$6:$O$6,0)+5) to CombinedPL!$C$6:$O$6,0)+2) because JAN is in column C and so there are two columns (A & B) to the left of the range that need to be adjusted for when determining the COLUMN reference. It had seemed so logical and hopeful!! Thanks anyway. Stephen "Frank Kabel" wrote: Hi with text values it would get complicated. To be honest: Better change your model once but to deal with too complicated formulas :-). Saying that you may try the following (though not tested): =SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou v er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6 :$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500)) -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thank you, Frank. You're very resourceful. Unfortunately, the models which are the source workbooks use text and not numerics for the dates (too much work to change every model). However, I could probably: a) modify your approach to identify DEC and subtract this from the twelve month total; or b) use the existing INDEX approach to do the same thing (12 months minus DEC). This would be a fix for now and I could re-consider the design of the source workbooks for next year's version. Thanks again. Stephen |
|
#14
|
|||
|
|||
|
Hi
if you like, email me a sample file and I'll have a look at it :-) -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Sorry, Frank. I can't get your suggestion to work. The succession of FALSE values evaluate to zero and are multiplied together to produce zero. I even changed your CombinedPL!$C$6:$O$6,0)+5) to CombinedPL!$C$6:$O$6,0)+2) because JAN is in column C and so there are two columns (A & B) to the left of the range that need to be adjusted for when determining the COLUMN reference. It had seemed so logical and hopeful!! Thanks anyway. Stephen "Frank Kabel" wrote: Hi with text values it would get complicated. To be honest: Better change your model once but to deal with too complicated formulas :-). Saying that you may try the following (though not tested): =SUMPRODUCT(([Vancouver.xls]CombinedPL!$B$7:$B$500=B$9)*(COLUMN([Vancou v er.xls]CombinedPL!$C$6:$O$6)<=MATCH($B$5,[Vancouver.xls]CombinedPL!$C$6 :$O$6,0)+5)*([Vancouver.xls]CombinedPL!$C$7:$O$500)) -- Regards Frank Kabel Frankfurt, Germany "Stephen POWELL" schrieb im Newsbeitrag ... Thank you, Frank. You're very resourceful. Unfortunately, the models which are the source workbooks use text and not numerics for the dates (too much work to change every model). However, I could probably: a) modify your approach to identify DEC and subtract this from the twelve month total; or b) use the existing INDEX approach to do the same thing (12 months minus DEC). This would be a fix for now and I could re-consider the design of the source workbooks for next year's version. Thanks again. Stephen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Update Links - Problem | Links and Linking in Excel | |||
| Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
| Baffling formula problem | Excel Discussion (Misc queries) | |||
| Links in formulas change when another user runs a workbook | Excel Discussion (Misc queries) | |||
| Why do my links break when I burn multiple Excel files to a CD? | Excel Worksheet Functions | |||
Linear Mode
