Problems with external links in Excel XP formulas

  • Thread starter Thread starter jc
  • Start date Start date
J

jc

Hi,
i'm trying to use formulas like sumif and countif with links to other files,
and this only works when the source file is open, otherwise it returns
#VALUE (the message with the external link confirmation appears I responde
"Update"!)
When I was using Office 2000 it worked well.
Regards.

Jorge Caneiras
 
I doubt that SUMIF and COUNTIF *ever* worked when the source sheet was
closed.

However, the function to use that *will work* on closed sheets is the IF
function.
You can combine it with SUM and COUNT and enter it as an array formula, CSE
(<Ctrl> <Shift> <Enter>) and be able to return your data from open or closed
sheets.

For example, change:

=SUMIF('FullPathSHEET1'!$A$5:$A$26,">10")

TO

=SUM(IF('FullPathSHEET1'!$A$5:$A$26>10,'FullPathSHEET1'!$A$5:$A$26,""))

You can perform exactly the same revision to a COUNTIF formula:

=COUNT(IF('FullPathSHEET1'!$A$5:$A$26>10,'FullPathSHEET1'!$A$5:$A$26,""))

Don't forget to use CSE.
If done correctly, the formula will automatically be enclosed in curly {}
brackets.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,
i'm trying to use formulas like sumif and countif with links to other files,
and this only works when the source file is open, otherwise it returns
#VALUE (the message with the external link confirmation appears I responde
"Update"!)
When I was using Office 2000 it worked well.
Regards.

Jorge Caneiras
 
I don't recall this changing behavior between xl2k and xl2002 (and I don't have
xl2k to check).

Something that may help (from a previous post):
==============================================

I think =sumif() dislikes closed files.

But maybe you could rewrite your formula using:
=SUM(IF('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8,
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30))
(all one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(I'd actually create the formula with both workbooks open, so I could just point
and click on the ranges. Then when you close the "file M" workbook, you'll see
the full path to the closed file.)

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)

=====================================
If it doesn't spark a solution for you, post back with your formula(s).

I'm sure you'll get a good response back.
 
Back
Top