Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete cell value but keep formula in cell.
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete cell value but keep formula in cell.
You need to explain that a bit.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Lofred" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete cell value but keep formula in cell.
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. "Lofred" wrote: 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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete cell value but keep formula in cell.
Hi Lofred,
Not possible - formulae necessarily return the results of their calculations. You could, of course, delete the data the formula refers to. -- Cheers macropod [Microsoft MVP - Word] "Lofred" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete cell value but keep formula in cell.
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 "Lofred" wrote: 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? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete cell value but keep formula in cell.
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 "Lofred" wrote in message ... 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 "Lofred" wrote: 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? __________ 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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete cell value but keep formula in cell.
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) "Don Guillett" wrote: If that is true it fully explains the overwhelming popularity of Quattro. -- Don Guillett Microsoft MVP Excel SalesAid Software "Lofred" wrote in message ... 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 "Lofred" wrote: 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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I delete data in a cell without deleting formula? | Excel Worksheet Functions | |||
Delete Empty Cell With Formula | Excel Discussion (Misc queries) | |||
How do I enter a formula that will delete a line when a cell = 0? | Excel Worksheet Functions | |||
Use formula to hide/delete cell? | Excel Worksheet Functions | |||
Can I Delete Symbol in Cell That Has A Formula? | Excel Discussion (Misc queries) |