If #REF! Use 0
 

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.

If #REF! Use 0
 

How is you ISERROR written? This should work:

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

-Slave2Six


--
Slave2Six
------------------------------------------------------------------------
Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904
View this thread: http://www.excelforum.com/showthread...hreadid=567233

If #REF! Use 0
 

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


"Slave2Six" wrote:


How is you ISERROR written? This should work:

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

-Slave2Six


--
Slave2Six
------------------------------------------------------------------------
Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904
View this thread: http://www.excelforum.com/showthread...hreadid=567233

If #REF! Use 0
 

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


KMH wrote:

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

"Slave2Six" wrote:


How is you ISERROR written? This should work:

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

-Slave2Six


--
Slave2Six
------------------------------------------------------------------------
Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904
View this thread: http://www.excelforum.com/showthread...hreadid=567233



--

Dave Peterson

If #REF! Use 0
 

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.


--
Slave2Six
------------------------------------------------------------------------
Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904
View this thread: http://www.excelforum.com/showthread...hreadid=567233

If #REF! Use 0
 

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" wrote:


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.


--
Slave2Six
------------------------------------------------------------------------
Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904
View this thread: http://www.excelforum.com/showthread...hreadid=567233

If #REF! Use 0
 

Remove ="True" from your statement.

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


--
Slave2Six
------------------------------------------------------------------------
Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904
View this thread: http://www.excelforum.com/showthread...hreadid=567233

If #REF! Use 0
 

Or even change it to:

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

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

Slave2Six wrote:

Remove ="True" from your statement.

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

--
Slave2Six
------------------------------------------------------------------------
Slave2Six's Profile: http://www.excelforum.com/member.php...o&userid=36904
View this thread: http://www.excelforum.com/showthread...hreadid=567233

--

Dave Peterson