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

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