Error in Formula iserror formula

[brian.baker13]

Hi

I have a formula containing 6 of the following statements uysing
different cells
=IF(ISERROR(-'S:\Site Labour\Site Labour 2008\Reports\Variance Report\
[Labour Variance Report wk01.xls]Summary'!$W$55),0,-'S:\Site Labour
\Site Labour 2008\Reports\Variance Report\[Labour Variance Report
wk01.xls]Summary'!$W$55

I do the above 6 times as I am referencing 6 cells some of which
contain #value! errors so I use the above formula to replace the error
with a 0

I have tried to update links in the spreadsheet to wk02.xls for
instance but get a message

formula is too long?

after doing this on the 6th statement as above I get funny characters
as such
ï¾°ISERROR(-'S:\Site Labour\Site Labour 2008\Reports\Variance Report\
[Labour Variance Report wk01.xls]Summary'!$W$82)矡

Could you please help

Thanks

Brian

 

[JE McGimpsey]

Can you get rid of the #VALUE! errors in Summary?

In general, it's bad form to have "expected" errors in your workbooks -
it tends to both mask unexpected errors (what if the error was #REF! or
#DIV/0 instead - your ISERROR() would ignore them, too), and to make
your users less likely to pay attention to "real" errors.

In other words, trap the error at the source, not downline.

If you can't, or won't do that, then with only 6 references, you can
easily use defined names. Choose Insert/Name/Define:

Reference in workbook: RefCell1
Refers to: ='S:\Site Labour...

then in your formula:

=IF(ISERR(RefCell1),0,RefCell1)

 

[Dave Peterson]

Another way around this is to use 6 different cells (in a hidden worksheet).

Then use a 7th cell to sum the first 6.

Then use that 7th cell in any formula in the other worksheets.

Hi

I have a formula containing 6 of the following statements uysing
different cells
=IF(ISERROR(-'S:\Site Labour\Site Labour 2008\Reports\Variance Report\
[Labour Variance Report wk01.xls]Summary'!$W$55),0,-'S:\Site Labour
\Site Labour 2008\Reports\Variance Report\[Labour Variance Report
wk01.xls]Summary'!$W$55

I do the above 6 times as I am referencing 6 cells some of which
contain #value! errors so I use the above formula to replace the error
with a 0

I have tried to update links in the spreadsheet to wk02.xls for
instance but get a message

formula is too long?

after doing this on the 6th statement as above I get funny characters
as such
ï¾°ISERROR(-'S:\Site Labour\Site Labour 2008\Reports\Variance Report\
[Labour Variance Report wk01.xls]Summary'!$W$82)矡

Could you please help

Thanks

Brian