Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sharepoint Linked File Causing #Value Error
I just spend the better part of the day creating a truly wonderful, yet
complex excel file, that had links to a second file I had open on my desktop. It worked wonderfully well. However, the second file is on sharepoint. When I re-opened my wonderful file, all the nice, complex calculated formulas that pointed at the sharepoint stored file all contain #VALUE, and now it no longer works. Anyone know how to address this behavior? I plan to share file 1 with many folks, having it pointing at a data table in that second excel file, but now, I cannot. (note: the links were not to pivoted cells in the second table...just some complex sumifs and sumproduct formulas). Help...just lost a day's work. Patk |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sharepoint Linked File Causing #Value Error
I should explain better... HEre is what one of my cell formulas looked like
when it was first created: =SUMIFS('[Program Tracker.xlsm]Detail'!$M:$M,'[Program Tracker.xlsm]Detail'!$A:$A,"="&$D$6,'[Program Tracker.xlsm]Detail'!$O:$O,"=No",'[Program Tracker.xlsm]Detail'!$F:$F,"="&$C30,'[Program Tracker.xlsm]Detail'!$U:$U,""&$D$2,'[Program Tracker.xlsm]Detail'!$U:$U,"<="&$D$3) Here is what it looked like after I saved the first file, then re-opened it: =SUMIFS('C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$M:$M,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$A:$A,"="&$D$6,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$O:$O,"=No",'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$F:$F,"="&$C30,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$U:$U,""&$D$2,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$U:$U,"<="&$D$3) Figured a "picture" might explain better than I did...thanks! Patk |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sharepoint Linked File Causing #Value Error
I don't know about SUMIFS because I don't have Excel 2007, but you
can't use SUMIF with an external closed file, and I suspect that is why you are getting #VALUE. When an external file is open, Excel only needs to have '[filename.xls] Sheet_name'!cell_ref to be able to reference a cell, but if it is closed then it needs the full path to the filename as well - this is why your formula has suddenly expanded. Can you not arrange to have the Program Tracker file open at the same time? Hope this helps. Pete On Nov 12, 1:25*am, PatK wrote: I should explain better... *HEre is what one of my cell formulas looked like when it was first created: =SUMIFS('[Program Tracker.xlsm]Detail'!$M:$M,'[Program Tracker.xlsm]Detail'!$A:$A,"="&$D$6,'[Program Tracker.xlsm]Detail'!$O:$O,"=No",'[Program Tracker.xlsm]Detail'!$F:$F,"="&$C30,'[Program Tracker.xlsm]Detail'!$U:$U,""&$D$2,'[Program Tracker.xlsm]Detail'!$U:$U,"<="&$D$3) Here is what it looked like after I saved the first file, then re-opened it: =SUMIFS('C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$M:$M,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$A:$A,"="&$D$6,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$O:$O,"=No",'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$F:$F,"="&$C30,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$U:$U,""&$D$2,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$U:$U,"<="&$D$3) Figured a "picture" might explain better than I did...thanks! Patk |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sharepoint Linked File Causing #Value Error
Well...I could, but the problem is really that it changes that formula. I
have to then do a find/replace to delete the fully qualified reference, to make it work again. I am trying to make it NOT do that. Is there something comparable to SUMIFS that would allow access to an close file? Also, please note that I mistakenly copied the wrong formula in my earlier post. I was "trying" somethings, like hosting the second file on my PC (not something I can do normally, as the file needs to be shared). But, pretend that every where where you see C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\ in my second post, really "normally" would read something like http:\\sharepointservername.mycompany.com\folderna me\ That is what is really happening. Anyway, you point on sumifs is helpful. Any alternatives? Thanks! Patk "Pete_UK" wrote: I don't know about SUMIFS because I don't have Excel 2007, but you can't use SUMIF with an external closed file, and I suspect that is why you are getting #VALUE. When an external file is open, Excel only needs to have '[filename.xls] Sheet_name'!cell_ref to be able to reference a cell, but if it is closed then it needs the full path to the filename as well - this is why your formula has suddenly expanded. Can you not arrange to have the Program Tracker file open at the same time? Hope this helps. Pete On Nov 12, 1:25 am, PatK wrote: I should explain better... HEre is what one of my cell formulas looked like when it was first created: =SUMIFS('[Program Tracker.xlsm]Detail'!$M:$M,'[Program Tracker.xlsm]Detail'!$A:$A,"="&$D$6,'[Program Tracker.xlsm]Detail'!$O:$O,"=No",'[Program Tracker.xlsm]Detail'!$F:$F,"="&$C30,'[Program Tracker.xlsm]Detail'!$U:$U,""&$D$2,'[Program Tracker.xlsm]Detail'!$U:$U,"<="&$D$3) Here is what it looked like after I saved the first file, then re-opened it: =SUMIFS('C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$M:$M,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$A:$A,"="&$D$6,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$O:$O,"=No",'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$F:$F,"="&$C30,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$U:$U,""&$D$2,'C:\Documents and Settings\pklocke\My Documents\01-IT Optimization\Planning\[Program Tracker.xlsm]Detail'!$U:$U,"<="&$D$3) Figured a "picture" might explain better than I did...thanks! Patk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges are causing a #REF error | Excel Discussion (Misc queries) | |||
Query causing #ref error in spreadsheet | Excel Discussion (Misc queries) | |||
OFFSET: Returns error when used to a linked file | Excel Worksheet Functions | |||
VLOOKUP Formula causing an error | Excel Discussion (Misc queries) | |||
Excel linked worksheets in Windows sharepoint services | Links and Linking in Excel |