Excel 2010 Returns VALUE# errors when updating links to other workbooks

Question

Excel 2010 Will not reference source documents to update external links unless the source documents are opens at the same time. Instead, when the dependent workbook is opened and you are prompted to update links from the source workbook, VALUE# errors are returned for all external links... Any ideas? I am using a SUMIF formula and it works perfectly fine, so long as both workbooks are open at the same time.

Thanks in advance for your assistance.

Answer 1

Hi, the problem is when the sheet is re-calculated if the workbook is closed it will give you an error, see here to read about sumif and sumproduct

http://xldynamic.com/source/xld.SUMPRODUCT.html#advantages

go to advantages of Sumproduct

try

=SUMPRODUCT(--('S:\Staff Shared Files\Board\WHRC Financial\Budget\MHRC\2012[11-12 Budget MHRC (Work File - Linked).xlsx]970 Sherb'!$A$7:$A$69=B18),'S:\Staff Shared Files\Board\WHRC Financial\Budget\MHRC\2012[11-12 Budget MHRC (Work File - Linked).xlsx]970 Sherb'!$S$7:$S$73)

Answer 2

NPO Controller wrote:

Do you know why sumif does not work with closed workbooks... It did with Excel 2003

Not my copy of XL2003 SP3.  However, it depends on when you look at the cell value.  This is pretty easy to demonstrate.

Start a new instance of Excel, and be sure that Automatic calculation mode is set (Tools > Options > Calculation).

Open a new worksheet (File > New), presumably called Book1, and fill A1:A10 with 1 through 10.

In the same instance of Excel, open another new worksheet, presumably called Book2, and enter the following formulas:

A1:  =SUMIF([Book1.xls]Sheet1!$A$1:$A$10,">=1")

A2:  =SUMPRODUCT(--([Book1.xls]Sheet1!$A$1:$A$10>=1),[Book1.xls]Sheet1!$A$1:$A$10)

Now save and close Book1.  Note that both A1 and A2 retain their values.  That might give you the illusion that SUMIF works on the closed workbook.  But now press ctrl+alt+F9 to recalculate.  You will see that A1 becomes #VALUE, while A2 retains its value.

Save and close Book2.  Open Book 2 again.  You will see that A1 is #VALUE, but A2 displays a value.  Note that the formulas in each of A1 and A2 refer to the full path name of the Book1 file.

Now open Book1, then look at A1 in Book2.  You will see that it now displays a value.

Answer 3

Thanks for your assistance, Much Appreciated!!!

Answer 4

Do you know why sumif does not work with closed workbooks... It did with Excel 2003, not sure about 2007 as i went directly from 03 to 10.

=SUMIF('S:\Staff Shared Files\Board\WHRC Financial\Budget\MHRC\2012[11-12 Budget MHRC (Work File - Linked).xlsx]970 Sherb'!$A$7:$A$69,B18,'S:\Staff Shared Files\Board\WHRC Financial\Budget\MHRC\2012[11-12 Budget MHRC (Work File - Linked).xlsx]970 Sherb'!$S$7:$S$73)

Answer 5

Hi,

Sumproduct will work with closed workbooks, could you post your formula