Link error #VALUE

[Fi]

I use Excel 2003 and have created two workbooks using Excel 2003.

One a workbook for standard users to input data. Therefore this is the
source book. Then created another workbook which links data to collate into
a report (refer to as destination workbook).

When the source and destinatation workbook are open the links work fine.
However if I only open the destination workbook and when prompted Update
links. All cells containing formula drawn from the source workbook appear as
#VALUE in the formula cell.

Can you let me know why this occurs and what I can do to prevent this. As
my understanding was when you open a linked destination workbook you can
update the links to the formulas recalc to show any changes since the
workbook was last opened.
Any thoughts on this gratefully received!

 

[bj]

it may be the functions you use, some require the link file to be open.

 

[Fi]

This is the formula
=COUNTIF([Review Spreadsheet.xls]Neighbourhood'!$D$2:$D$215,'Yes or No'!A2)

Do countif function require both workbooks open to ensure they work correctly?

If this is the case can you point me in the direction for further support on
such formulas that require both workbooks to be open against those that can
work independantly of the source file being open?

 

[bj]

I have never seen a list which details which ones work and don't work. It is
also different depending on which verison of Excel you have.
I beleive that countif, and sumif do need it to be open

This is the formula
=COUNTIF([Review Spreadsheet.xls]Neighbourhood'!$D$2:$D$215,'Yes or No'!A2)

Do countif function require both workbooks open to ensure they work correctly?

If this is the case can you point me in the direction for further support on
such formulas that require both workbooks to be open against those that can
work independantly of the source file being open?

it may be the functions you use, some require the link file to be open.

 

[Dave Peterson]

Yep.

=countif(), =sumif(), indirect() are 3 of the functions that won't work when the
sending workbook is closed.

There are alternatives:

=sumproduct(
--('[Review Spreadsheet.xls]Neighbourhood'!$D$2:$D$215='Yes or No'!A2))

Create this formula with that sending workbook open. Excel will update the
formula to include the path when you close it.

=sumproduct() likes to work with numbers.

The -- stuff converts trues and falses to 0's and 1's.

This is the formula
=COUNTIF([Review Spreadsheet.xls]Neighbourhood'!$D$2:$D$215,'Yes or No'!A2)

Do countif function require both workbooks open to ensure they work correctly?

If this is the case can you point me in the direction for further support on
such formulas that require both workbooks to be open against those that can
work independantly of the source file being open?

it may be the functions you use, some require the link file to be open.

 

[Fi]

Many thanks I found help on Microsoft site and this is the recommendation:

SYMPTOMS
A formula that contains the SUMIF, COUNTIF, or COUNTBLANK functions may
return the #VALUE! error in Microsoft Excel.

CAUSE
This behaviour occurs when the formula that contains the SUMIF, COUNTIF, or
COUNTBLANK function refers to cells in a closed workbook.

WORKAROUND
To work around this behaviour, use a combination of the SUM and IF functions
together in an array formula.

COUNTIF
Instead of using a formula that is similar to the following
=COUNTIF([Source]Sheet1!$A$1:$A$8,"a")
use the following formula:
=SUM(IF([Source]Sheet1!$A$1:$A$8="a",1,0))
Apply an array so it will appear as
{=SUM(IF([Source]Sheet1!$A$1:$A$8="a",1,0))}

ADDITIONAL INFO:
You will need to remove all merged cells to ensure the array works.

Yep.

=countif(), =sumif(), indirect() are 3 of the functions that won't work when the
sending workbook is closed.

There are alternatives:

=sumproduct(
--('[Review Spreadsheet.xls]Neighbourhood'!$D$2:$D$215='Yes or No'!A2))

Create this formula with that sending workbook open. Excel will update the
formula to include the path when you close it.

=sumproduct() likes to work with numbers.

The -- stuff converts trues and falses to 0's and 1's.

This is the formula
=COUNTIF([Review Spreadsheet.xls]Neighbourhood'!$D$2:$D$215,'Yes or No'!A2)

Do countif function require both workbooks open to ensure they work correctly?

If this is the case can you point me in the direction for further support on
such formulas that require both workbooks to be open against those that can
work independantly of the source file being open?

it may be the functions you use, some require the link file to be open.

:

I use Excel 2003 and have created two workbooks using Excel 2003.

One a workbook for standard users to input data. Therefore this is the
source book. Then created another workbook which links data to collate into
a report (refer to as destination workbook).

When the source and destinatation workbook are open the links work fine.
However if I only open the destination workbook and when prompted Update
links. All cells containing formula drawn from the source workbook appear as
#VALUE in the formula cell.

Can you let me know why this occurs and what I can do to prevent this. As
my understanding was when you open a linked destination workbook you can
update the links to the formulas recalc to show any changes since the
workbook was last opened.
Any thoughts on this gratefully received!