Extrenal Links not updated unless file is open simaltaneously!

[M Imran Buhary]

Hi All,

I have a file with links to other workbooks in same directories and
different directories all files are in the server, coming under one tree. The
links refer to single cells and sometimes comprise formulas like Vlookup or
sumif etc. When I open the destination file some links are updated but others
results are returned as " #Value! ". Buy if I open the source file also same
time the results are shown properly. What could be the problem here ?

 

[Ragdyer]

Some functions don't work on closed WBs.

Indirect, Sumif, Countif, ... just to name a few.

There are usually "work-around" alternatives.

Post your formulas that are returning the #Value! error, and we'll see what
other options might be available.

 

[M Imran Buhary]

=SUMIF('Q:\Treasury\Imran\Fixed Deposits\Weekly Reports - September
2007\[Weekly Report 30 September 2007.xls]Accrued
Interest'!$C$2:$C$15,A22,'Q:\Treasury\Imran\Fixed Deposits\Weekly Reports -
September 2007\[Weekly Report 30 September 2007.xls]Accrued
Interest'!$V$2:$V$15)

 

[Dave Peterson]

=SUMproduct(--('yourpath[yourworkbook]yoursheet'!$C$2:$C$15=A22),
'yourpath[yourworkbook]yoursheet'!$V$2:$V$15)

(It'll be easier to get the formula to work if the sending workbook is open.
Then when you close that workbook, excel will adjust the formula for you.)


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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=SUMIF('Q:\Treasury\Imran\Fixed Deposits\Weekly Reports - September
2007\[Weekly Report 30 September 2007.xls]Accrued
Interest'!$C$2:$C$15,A22,'Q:\Treasury\Imran\Fixed Deposits\Weekly Reports -
September 2007\[Weekly Report 30 September 2007.xls]Accrued
Interest'!$V$2:$V$15)

--
M Imran Buhary

Some functions don't work on closed WBs.

Indirect, Sumif, Countif, ... just to name a few.

There are usually "work-around" alternatives.

Post your formulas that are returning the #Value! error, and we'll see what
other options might be available.