MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Retaining value in cell after condition goes becomes false.


Posted by Twood on November 01, 2001 10:12 AM

Hi,
I have an if statement in a cell which places a value in a cell when true and leaves it blank when false. The problem is that I want the resultant value the condition is true to stay in the cell, even if the condition for the if statement then becomes false. Any ideas anyone?


Posted by Todd on November 01, 2001 10:31 AM

Could you please clarify? I lost you after the first sentence. Perhaps include sample cell references, values, and formulas.

Posted by Twood on November 01, 2001 10:45 AM

Ok,
Suppose my cell contains:
IF('Sheet1'!A1="Apples","Fruit","")
meaning that if cell A1 of Sheet1 contains the word "Apples" then the cell containing this formula gets the value "Fruit". If I change the value of cell A1 in Sheet1 then the current cell goes blank as the condition is now false. I would like the word "Fruit" to stay even if it changes(ie) even if the condition evaluates to false at some point afterwards.
Is this any clearer? I would appreciate any help you could provide.

Posted by Mark W. on November 01, 2001 10:50 AM

You seem to be laboring with some misconceptions...

1. An IF worksheet function cannot "[leave] it
[the cell] blank when false". =IF(1=2,TRUE,"")
will produce an empty text string (i.e., one
with a length of 0) that appears to be blank,
but isn't. A truly blank cell won't return
TRUE when referenced by the ISTEXT() or
ISNUMBER() boolean functions.

2. A cell will always contain 1 result -- the
result when formula was last recalculated;
therefore, you can't retain a previously
produced value when the IF function's condition
changes.

Posted by Todd on November 01, 2001 11:07 AM

Assuming you're not going to use macros at all,
once you use the function, and it evaluates, do Ctrl-C, then Alt-E-S-V-enter. It will change the contents of the cell from the formula to its value. That's the only way I know how.

Posted by Twood on November 01, 2001 11:12 AM

Re: You seem to be laboring with some misconceptions...


Fair enough. Is there perhaps another function that may be able to do what I need done?

Posted by Mark W. on November 01, 2001 11:30 AM

Re: You seem to be laboring with some misconceptions...

No, all of Excel's worksheet functions behave as
described below.