#Value error when linking workbooks

[FOLALD]

Hello All,

Hope you can assist.

Excel 2003, SP1
XP Home



---I am using '=Countif' to extract only 1 criteria from another
workbook, and from 1 column only.

--- =COUNTIF([Book2.xls]Sheet1!$B:$B,A2)

---Both Source & Formula bboks are same version.

---When source book is open, formula returns correct results.

---When source book closed & formula book is closed & opened, I get the
Unable to find source error.

---To get around this for now, I am using the edit, links, startup
prompt as the sheet will remain unchanged for the moment [until I sort
this issue out ]

=========
Question is?
=========

Does the Countif function enable source sheets to update automatically.
If not, is there a formula that does?


Thanks for taking the time to read this,

Regards

 

[Dave Peterson]

=countif() will work with open workbooks.

It'll update as ofter as you recalculate
(tools|options|calculation tab|check automatic???)

You could use a different worksheet function:

=sumproduct(--([Book2.xls]Sheet1!$B1:B999=A2))

You can't use the whole column, though.

(Build the formula with Book2.xls open and excel will adjust the formula when
you close that workbook.)

Hello All,

Hope you can assist.

Excel 2003, SP1
XP Home

---I am using '=Countif' to extract only 1 criteria from another
workbook, and from 1 column only.

--- =COUNTIF([Book2.xls]Sheet1!$B:$B,A2)

---Both Source & Formula bboks are same version.

---When source book is open, formula returns correct results.

---When source book closed & formula book is closed & opened, I get the
Unable to find source error.

---To get around this for now, I am using the edit, links, startup
prompt as the sheet will remain unchanged for the moment [until I sort
this issue out ]

=========
Question is?
=========

Does the Countif function enable source sheets to update automatically.
If not, is there a formula that does?

Thanks for taking the time to read this,

Regards