Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 18th 07, 06:56 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 7
Default 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


  #2   Report Post  
Old June 18th 07, 08:06 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default 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



  #3   Report Post  
Old June 18th 07, 12:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default 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



  #4   Report Post  
Old June 18th 07, 08:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 7
Default 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

  #5   Report Post  
Old June 18th 07, 09:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default 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





  #6   Report Post  
Old June 18th 07, 09:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to delete cell values without affecting cell formulae perfection Excel Discussion (Misc queries) 0 June 18th 07 06:55 AM
Macro Help Needed: Comparing cell values and deleting rows [email protected] Excel Discussion (Misc queries) 1 September 19th 06 02:39 AM
I can't delete a cell without deleting a row Tenny Excel Discussion (Misc queries) 2 June 30th 06 12:52 AM
Delete values from cell automatically ernie Excel Discussion (Misc queries) 5 February 15th 06 10:01 PM
delete values in several cells without deleting the formulas dranreb Excel Discussion (Misc queries) 4 December 9th 04 02:15 AM


All times are GMT +1. The time now is 09:08 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017