ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How to delete cell values withour deleting cell formulae (https://www.excelbanter.com/excel-discussion-misc-queries/146831-how-delete-cell-values-withour-deleting-cell-formulae.html)

perfection June 18th 07 06:56 AM

How to delete cell values withour deleting cell formulae
 
I wish to use a previous years excel sheet but i need to blank out all
entered and caluclated values present. If i do a complete delete i
will lose all the formulae that have vbeen inserted into certain
formulae. How can i delete all values withour the formulae getting
deleted


David Biddulph[_2_] June 18th 07 08:06 AM

How to delete cell values withour deleting cell formulae
 
One option is Edit/ Goto/ Special/ Constants, then delete.
--
David Biddulph

"perfection" wrote in message
oups.com...
I wish to use a previous years excel sheet but i need to blank out all
entered and caluclated values present. If i do a complete delete i
will lose all the formulae that have vbeen inserted into certain
formulae. How can i delete all values withour the formulae getting
deleted




Jon Peltier June 18th 07 12:32 PM

How to delete cell values withour deleting cell formulae
 
Do you want to hide the calculated values? Suppose cell B1 is based on A1.
Delete the contents of A1, and change the formula in B1 from

=<whatever

to

=IF(LEN(A1)=0,"",<whatever)

You might consider some kind of unique formatting (e.g. light blue shading)
in A1 and other user-input cells so the user knows where to enter inputs.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"perfection" wrote in message
oups.com...
I wish to use a previous years excel sheet but i need to blank out all
entered and caluclated values present. If i do a complete delete i
will lose all the formulae that have vbeen inserted into certain
formulae. How can i delete all values withour the formulae getting
deleted




perfection June 18th 07 08:45 PM

How to delete cell values withour deleting cell formulae
 
On Jun 18, 10:56 am, perfection wrote:
I wish to use a previous years excel sheet but i need to blank out all
entered and caluclated values present. If i do a complete delete i
will lose all the formulae that have vbeen inserted into certain
formulae. How can i delete all values withour the formulae getting
deleted


David
You did understand what i need to do but this merely locates cells
that have formulae and highlights them. how do delete en masse w/out
distubing the formulae cells


David Biddulph[_2_] June 18th 07 09:03 PM

How to delete cell values withour deleting cell formulae
 
You seem to have replied to your own message. If you intended to reply to
my message, I'll repeat the content he
"One option is Edit/ Goto/ Special/ Constants, then delete."

Did you try what I suggested? It doesn't highlight the cells that have
*formulae*, it highlights the cells that have constant *values*, then if you
hit delete it will delete those values, and leave the formulae in place,
which is what you asked for. Perhaps you need to re-read the suggestion and
try again?
--
David Biddulph

"perfection" wrote in message
oups.com...

David
You did understand what i need to do but this merely locates cells
that have formulae and highlights them. how do delete en masse w/out
distubing the formulae cells


On Jun 18, 10:56 am, perfection wrote:
I wish to use a previous years excel sheet but i need to blank out all
entered and caluclated values present. If i do a complete delete i
will lose all the formulae that have vbeen inserted into certain
formulae. How can i delete all values withour the formulae getting
deleted




Gord Dibben June 18th 07 09:05 PM

How to delete cell values withour deleting cell formulae
 
David wrote "One option is Edit/ Goto/ Special/ Constants, then delete."

Note the word "constants" not formulas.


Gord Dibben MS Excel MVP

On Mon, 18 Jun 2007 12:45:51 -0700, perfection
wrote:

On Jun 18, 10:56 am, perfection wrote:
I wish to use a previous years excel sheet but i need to blank out all
entered and caluclated values present. If i do a complete delete i
will lose all the formulae that have vbeen inserted into certain
formulae. How can i delete all values withour the formulae getting
deleted


David
You did understand what i need to do but this merely locates cells
that have formulae and highlights them. how do delete en masse w/out
distubing the formulae cells




All times are GMT +1. The time now is 12:15 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com