If #REF! Use 0

[Guest]

I have a template that I have developed where the users sometimes want to
delete a column that is linked into some calculations that are used in a
sumproduct range.

Temporarily, I have locked them out of deleteing the column, but it should
be easy to write an If Formula to make this situation ok.

I would like to have those calculations be an IF statement of some sort that
will detect that the cell that is referenced is a #REF now and therefore just
use 0 rather than make everything that uses that sumproduct range go to a
#REF.

I have tried ISERROR(cellname) or ISREF(cellname), and they seem to work
fine in a cell by themselves changing from True to False, but when I put them
inside an IF statement or just reference that a cell that has that
calculation in it, it still doesn't work. Why?

I could also write a macro to just over write the existing formula, but how
do I detect that the cell is deleted. I could use a Worksheet_Change
subroutine but then what syntax should I use for the If statement to detect
that the cell has been deleted?

Thanks for any help.

 

[Slave2Six]

How is you ISERROR written? This should work:

=IF(ISERROR(cellID),0,[function])

-Slave2Six

 

[Guest]

That's exactly the function I put in, but it doesn't work. Thanks for any
help.

 

[Dave Peterson]

You may want to share the original formula, the cell/column deleted, and the
formula you tried that contained the =if(iserror()...

That's exactly the function I put in, but it doesn't work. Thanks for any
help.

How is you ISERROR written? This should work:

=IF(ISERROR(cellID),0,[function])

-Slave2Six

 

[Slave2Six]

You may want to try adding a column for the IF statement. Example:

Column A: [Data Entry 1]
Column B: [Data Entry 2]
Column C: =A1*B1
Column D: =IF(ISERROR(C1), 0, C1)

Hide column C.

If either column A or B is deleted, the formula in C will result in an
error thus rendering a 0.

 

[Guest]

Here's the formula....

cell P483 = cellname * 10%
=IF(ISERROR(P483)="True",0,P483)

when I delete the column with cellname in it, everything goes #REF!.

Thanks for all your help. I feel better because I thought it should be this
easy as well but it's not working

 

[Slave2Six]

Remove ="True" from your statement.

The function should be =IF(ISERROR(P483),0,P483)

 

[Dave Peterson]

Or even change it to:

=IF(ISERROR(P483)=TRUE,0,P483)

(But I'd use your suggestion, too.)