|
Posted to microsoft.public.excel.links
|
|
|
A link between two workbooks doesn't keep the value, shows #va
"Dave Peterson" wrote:
There are some functions that won't work when the sending workbook is closed:
=indirect(), =sumif(), =countif() are a few.
You could replace the =sumif() formula with the equivalent =sumproduct()
=sumif(a:a,"x",b:b)
could be replaced with
=sumproduct(--(a:a="x"),(b:b))
And because you're working in xl2007, you can use the whole column.
.....
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Liviu Dinu wrote:
Right, pure links works; but if you try to use that link in formulas (my case
sumif) doesn€„¢t work anymore until you open source file too.
I think problems came from translation (composing) path.
"Bill Manville" wrote:
Some links work, don't they?
Are the ones that don't work more complicated formulas (e.g. VLOOKUP)?
Some formulas are just too difficult for Excel to compute without
having the source workbook open.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
--
Dave Peterson
|