Clear Cell Without Removing Formula

[Guest]

Is there a way to preven Excel from removing a formula in a cell when just
clearing what is being displayed?

For example:
I have =IF(ISERROR(SUM(AG3+$AL$3-AH3)),"",SUM(AG3+$AL$3-AH3)) in a cell. The
users need to be able to clear the result without deleting the formula. Can
this be done? Thanks in Advance

(We're running Office 2003)

Thanks

 

[Guest]

To clear the display of a formula without removing the formula:

Set the color of the font the same as the color of the cell background.

 

[Guest]

Thanks for the reply, but that's not what I'm looking for.

What I mean is this:

If I have IF(ISERROR(SUM(AG3+$AL$3-AH3)),"",SUM(AG3+$AL$3-AH3)) in a cell
and that cell returns a value of 15 (for example), I want to be able to erase
the value of 15 while leaving the formula in tact. If I go to the cell and
click deltete, the formula and the value are gone. I just want to remove the
value (15)

Thanks.

 

[Pete_UK]

The value returned is the sum of AG3, AL3 and -AH3, so if you make one
of these cells produce an error then the SUM function would also
produce an error and because you have ISERROR in you formula then ""
would be returned (i.e. you won't see anything).

How you make any of the 3 cells produce an error depends on how they
get their values.

Pete

 

[Peo Sjoblom]

You have to clear out the cells that are part of the formula that returns
the value of 15
F5, special and constants

 

[Harlan Grove]

Scott wrote...

Thanks for the reply, but that's not what I'm looking for.

No, what's you're looking for is the metaphorical equivalent of being
able to have your cake and eat it too. Can't be done.

What I mean is this:

If I have IF(ISERROR(SUM(AG3+$AL$3-AH3)),"",SUM(AG3+$AL$3-AH3)) in a cell
and that cell returns a value of 15 (for example), I want to be able to erase
the value of 15 while leaving the formula in tact. If I go to the cell and
click deltete, the formula and the value are gone. I just want to remove the
value (15)

What the cell displays and what the cell contains aren't separate
things. You can only have one with the other, and you can only get rid
of one by getting rid of both. In other words, THERE IS *NO* *WAY* TO
PRESS [DELETE] AND GET RID OF ONLY WHAT'S DISPLAYED BUT PRESERVE THE
FORMULA. If the formula remains, Excel will display the formula's
value. The only way to alter this is to change the cell's formatting.
Putting it yet another way, pressing [Delete] clears the cell's
contents, in this case its formula, and by eliminating the formula
there's nothing left for Excel to display. Pressing [Delete] doesn't
just remove the value. Never has and likely never will.

There may be alternatives. What purpose do you believe would be served
by allowing users just to clear values?

 

[Guest]

you can't clear the result of a formula. You can only hide it (match the
text to the background, hide the row or column, etc.). Why would you want to
do that? Maybe if you can answer that, we might be able to give you a viable
workaround.

For example if you don't want a cell to be included in a SUM formula, you
can use SUBTOTAL which ignores cells that have been filtered out.