Deleting information in a cell without losing the formula

[annew]

Hi,

This is a function I have just taken over in my office. If I have a very detailed spreadsheet with many formulas that I have performed a save as for the 2004 year. I now need to delete the numbers in the cells without losing all the formulas. How do I do this?

Thank you,
Anne

 

[TonyJollans]

Hi Anne,

You can't quite do that. By definition you see the results of the formulae in the cells. If you clear the values the formulae are based on, the results of the formulae should clear themselves.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[annew]

Tony,

I am confused by your response if what I am attempting to do can or cannot be done. Apparently the person in my position before me did this successfully each year for this spreadsheet. I deleted the numbers (results of the formula) in the cells that had a formula associated with it. Before I deleted the numbers in the cell, when I clicked on that cell, the formula appeared in the pane above the Excel spreadsheet. After deleting the numbers in that cell, then clicking in the same cell again, there was no formula in the pane which tells me I have deleted the formula for that cell with the numbers.

Anne

 

[TonyJollans]

Hi Anne,

I guess we're misunderstanding each other here.

(Non-empty) cells either contain values or formulae. Formulae are based on other cells (which themselves either contain values or formulae).

Normally the cells which contain formulae will show as numbers (or text) in the cell, but will show as formulae in the box above. If you delete the content of the cell, it's gone - number and formula. This can't be what you want.

If the non-formula values are deleted then the results of the formulae may well be apparently empty cells. If this is what you want to do, then this should do it ..

Select Edit > Goto from the Menu
Click on Special
Select Constants
Press Delete

If I am still misunderstanding, please post back




Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[KenWright]

Only thing I'd add to Tony's post is that if you would rather set all those constants to 0 as opposed to deleting them, then after having run througfh Tony's method and got to the select constants bit

Select Edit > Goto from the Menu
Click on Special
Select Constants

Do that, type 0 and then hit CTRL+ENTER which will replace them all with a 0. Just in case it is easier to see where the data needs to go that way.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------