Sharepoint Linked File Causing #Value Error

  • Thread starter Thread starter PatK
  • Start date Start date
P

PatK

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
 
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
 
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
 
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\foldername\ That is
what is really happening.

Anyway, you point on sumifs is helpful. Any alternatives?

Thanks!

Patk

Pete_UK said:
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

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
Click to expand...
Click to expand...
 
Back
Top