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
|