The array formula:
=sum(if(....

Entered with ctrl-shift-enter may work for you.

Or =sumproduct() may work for you.

(But you can't use the whole column in any of your ranges for either of these
alternatives.)

This is an example from a different question:

=sumproduct(--('yourpath\[wkbk1.xls]sheet1!B10:B100=c2),
('yourpath\[wkbk1.xls]sheet1!F10:F100))

If you build your formula with the other workbook open, it'll be easier to
debug.



coop4_oppenheimer wrote:

Hi Dave.

I was trying to do some sumif formulas that grab information from a
spreadsheet on a network place (e.g. W:\some_name\sourcefile.xls). If the
source spreadsheet is closed, the links dont work and i get a #VALUE error.
Do you know some way to make things work? I cant have the source file opened
all the time, and a lot of people use spreadsheets that have links to the
source one. As i need to update the source spreadsheet, no one can open it
before me, or else i get a 'read-only" message.
Can you see any way to make things work?

Thank you a lot.

PS: i couldnt find any way to make this damn thing work! I thing i´m going
to develop some macros to do the "disrty work" to me.. =)

"Dave Peterson" wrote:

Some formulas don't work with closed workbooks (=indirect(), =countif(),
=sumif() are a few).

What do your formulas look like when both workbooks are open?

ovidiu wrote:

it still doesn't work, even if i click to update the values. the values
appear only when i open the source document

"CyberTaz" wrote:

In the file that contains the Links, go to EditLinks & you should find what
you need.

HTH |:)

"ovidiu" wrote:

I did some formulas with figuers from other files. the problem is that when i
open the sheet the links to the other documents don't work anymore. i have to
open the source sheets so that the figuers appear. how can i make the links
work even if the source sheets are not opend??? Thx, Ovidiu


--

Dave Peterson


--

Dave Peterson