| 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 |
| 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 | |||
Hybrid Mode
