Delete cell value but keep formula in cell.

[Lofred]

After the formula in a cell has calculated a value I would like to reset the
cell for the next use by deleting the value but keeping the formula in the
cell.
Is this possible in Excel 2007?

 

[Don Guillett]

You need to explain that a bit.

 

[JLatham]

It would be interesting to see what formula you are thinking of using in this
manner.

Remember that generally a cell can contain EITHER a formula OR a value, not
both at the same time. But with some VB magic, you can capture the value
that a formula generates and put it somewhere else and then do other things,
such as 'resetting' the values that the formula uses to generate the value.

For example, perhaps you have a simple formula like:
=SUM(A2:B2)
so it gives you the total of A2+B2 when anyone types a value into either A2
or B2. You could trigger off of that to save the answer the formula comes up
with and then erase the entries in A2 and B2.

 

[macropod]

Hi Lofred,

Not possible - formulae necessarily return the results of their calculations. You could, of course, delete the data the formula
refers to.

 

[Lofred]

I am new to Excel 2007 converting from Quattro Pro X3. QP has a useful
feature which allows you to zero out the value in a cell, but keep the
formula intact. This resets the spreadsheet for the next time period. The
process is as follows;

1. Select a range of cells.
2. Click EDIT > Cut
3. Click EDIT > Paste special
4. Disable the NUMBER CELLS checkbox.
5. Click PASTE
The value in the cell now turns to zero with the formula intact.

A sample formula in cell A3 would be; =IF(A1=1,A2,A3)
where A1=1 A2=5 resulting in A3=5
The value 5 would stay in A3 unless you deleted the formula in A3 and
reentered it.
A nested IF statement would be a work-a-round but it would make the formulas
more complex. I was hoping Excel had a similar process but I have not found
it.
Lofred

 

[Don Guillett]

If that is true it fully explains the overwhelming popularity of Quattro.

 

[Roger Govier]

Hi
Am I missing the point here?
Don't you achieve exactly the same outcome if you select your range of cells
(A1:A2) and press Delete?

--
Regards
Roger Govier

I am new to Excel 2007 converting from Quattro Pro X3. QP has a useful
feature which allows you to zero out the value in a cell, but keep the
formula intact. This resets the spreadsheet for the next time period. The
process is as follows;

1. Select a range of cells.
2. Click EDIT > Cut
3. Click EDIT > Paste special
4. Disable the NUMBER CELLS checkbox.
5. Click PASTE
The value in the cell now turns to zero with the formula intact.

A sample formula in cell A3 would be; =IF(A1=1,A2,A3)
where A1=1 A2=5 resulting in A3=5
The value 5 would stay in A3 unless you deleted the formula in A3 and
reentered it.
A nested IF statement would be a work-a-round but it would make the
formulas
more complex. I was hoping Excel had a similar process but I have not
found
it.
Lofred




__________ Information from ESET Smart Security, version of virus
signature database 4736 (20100101) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[JLatham]

Imagine how great such a thing would be at times with some new worksheet
function to do the work in some way: Call it a "PUSH()" function:
Enter a formula wrapped in it as =PUSH(IF(A1=1,A2,A3),AA1)
It calculates based on the internal formua, "pushes" the result into the
location that's the second part of the formula and resets its display to zero
until one of its dependencies changes. Really slick if it not only pushed
off to that second parameter cell, but then began pushing past values down
into the column so long as it had colums to push into, at which point the
value in the Rows.Count row would simply fall off and the others push down
continuously. Or maybe a 3rd parameter to indicate just how many previous
values to retain:
=PUSH(AVERAGE(A5:A100),AA1,10)