#Value Error Received on Linked Cells When Data Source is Closed

[mkuusik]

Hi All,

looking for a resolution - I don't know why this is happening, but I'm using
SUMIF formulae that references external links (source data is in external
file). The correct value is retrieved when the source file is open, however,
when the workbook is closed, I get a #VALUE error. But if I open the external spreadsheet
that it is linked to, then the #VALUE error disappears and the correct value
displays.

Any ideas on what is causing this and how to fix this? I prefer not to have to have both files open to work on the linked file. Thanks Mark </pre>

 

[schielrn]

Welcome to the Board!

See here, in regards to sumif and closed workbooks, it also gives you an array formula workaround:

http://support.microsoft.com/kb/260415

Hope that helps.

 

[mkuusik]

Thanks so much, that works. Why is sumif designed this way? Should I not use sumif going forward? Just trying to understand when or if to use it. Thanks again!

 

[schielrn]

Should I not use sumif going forward?

Sumif is much easier to write than the other formula and pre-2007 array formulas like the work around cannot use whole column references, whereas sumif cannot.

Array formulas are very useful and can be better than there counter parts in some aspects because you could sum on multiple criteria using the array formula, where as you cannot using sumif, but can in 2007 and beyond with sumifs.

Array formulas I also think are a little slower in calculating than using sumif, but not sure if you would notice that noticable of a difference.

As far as why it is designed that way I do not know.

Hope that helps.

 

[Tigvee]

Welcome to the Board!

See here, in regards to sumif and closed workbooks, it also gives you an array formula workaround:

http://support.microsoft.com/kb/260415

Hope that helps.

I was not able to get the array version of sumif to work when pulling values horizontally.

 

[seeparag]

Sorry to ask you this. But I could not reach to the solution in this link. Would you kindly elaborate how to navigate in this help section. I could not reach to workaround that would help in this scenario.