#Value error when linking workbooks

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


--
FOLALD


------------------------------------------------------------------------
FOLALD's Profile: http://www.excelforum.com/member.php...o&userid=27191
View this thread: http://www.excelforum.com/showthread...hreadid=466979

=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.)

FOLALD wrote:

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

--
FOLALD

------------------------------------------------------------------------
FOLALD's Profile: http://www.excelforum.com/member.php...o&userid=27191
View this thread: http://www.excelforum.com/showthread...hreadid=466979

--

Dave Peterson