How to clear a value but not the formula?

[Guest]

Hi,

I want to delete the value in a cell that has been entered, but not the
underlying formula? Can I do this?

Example - Cell A1 has the formula of =B1
I type in the value of '10' into B1, so A1 automatically becomes '10', too
Now, I want to remove the value of '10' from A1, but not B1

If I go to cell A1 and hit delete, the value of '10' is removed AND the
formual is removed.

How can I delete the value of '10', but leave the formual of =B1?

THANKS!

 

[Trevor Shuttleworth]

You can't. The formula in cell A1 reflects the value in B1. The only way
for A1 to appear blank is if B1 is blank.

Regards

Trevor

 

[Dave Peterson]

You could change the formula so that the cell looks empty if you clean up the
"sending" cell.

=if(b1="","",b1)

And now if you clean up B1, then A1 will be "cleaned", too.

 

[Guest]

What you're asking doesn't really make sense. You want A1 to equal B1, but
then you also don't want A1 to equal B1.

Here's one option the may help. Use a third cell as a toggle for cell A1.
For example:

In A1, use this formula: =IF(C1="X",B1,"")

Now, if you want A1 to be "turned on" (show the value of B1, then type an X
in C1. If you want A1 to be "turned off" (blank) then delete the X from C1.

HTH,
Elkar

 

[macropod]

Hi Shelly,

You could set calculation to 'manual' before deleting the value in A1. See
under Tools|Options|Calculation. Mind you, this setting affects all
calculations on all open workbooks. And, if you reset to 'Automatic' for
another workbook while the workbook you're concerned with is open, then B1
will be updated.

Cheers

 

[David Biddulph]

If you want to retain the value in B1, but not have the formula linking it
to A1, copy B1, and Paste Special/ Values. You can then delete A1 without
it upsetting B1.

 

[Ron Rosenfeld]

Hi,

I want to delete the value in a cell that has been entered, but not the
underlying formula? Can I do this?
No.


Example - Cell A1 has the formula of =B1
I type in the value of '10' into B1, so A1 automatically becomes '10', too
Now, I want to remove the value of '10' from A1, but not B1

If I go to cell A1 and hit delete, the value of '10' is removed AND the
formual is removed.

How can I delete the value of '10', but leave the formual of =B1?

You cannot.

Depending on what you are trying to accomplish, you could use a macro to
perform a copy B1 / Paste Special Values A1 type of routine, which would
accomplish something similar to what you describe.

This macro could be triggered by the "event" of entering a value into B1; or
something else.


--ron

 

[macropod]

Hi David,

That kills the formula, which the OP wants to retain!

Cheers