| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
