Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 23rd 06, 07:36 PM posted to microsoft.public.excel.misc
MrQuick
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone.

Is it possible to delete a cell's VALUE without deleting (or "Clearing") the
cell's formula?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

  #2   Report Post  
Old January 23rd 06, 07:42 PM posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone.

Yes: multiply by zero.

  #3   Report Post  
Old January 23rd 06, 08:47 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone.

Make your formula return what looks like blank when you don't need it:

=Sheet1!A1+sheet1!b99
may become

=if(sheet1!a1+sheet1!b99=0,"",sheet1!a1+sheet1!b99 )

But once you put in a formula, it's gonna have to evaluate to something...

MrQuick wrote:

Is it possible to delete a cell's VALUE without deleting (or "Clearing") the
cell's formula?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc


--

Dave Peterson
  #4   Report Post  
Old January 23rd 06, 08:49 PM posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone.

F5SpecialConstants. Uncheck/check what you want to delete and OK

EditClear Contents.


Gord Dibben MS Excel MVP

On Mon, 23 Jan 2006 10:36:04 -0800, "MrQuick"
wrote:

Is it possible to delete a cell's VALUE without deleting (or "Clearing") the
cell's formula?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc


  #5   Report Post  
Old January 25th 06, 01:40 AM posted to microsoft.public.excel.misc
MrQuick
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone

~ In the "Go To Special" dialog box, I clicked the empty circle w/"Constants"
next to it.
~ "Formulas" remained unselected.
~ The 4 boxes sub to "Formulas" became operative and were all checked: they
are... Numbers/Text/Logicals/Errors.
~ What boxes do I UNCHECK so as to allow me to delete a cell's value but
leave the formula alone after "OK" and then clicking EditClear Contents
~ Also, when should I select the cell to be operated on? Before hitting
F5...? Before EditClear Contents?

PS: My idea is for the "Edit" menu have a "Delete Special" or something like
that so users can decide do delete everything (values, formula, formatting)
or any one or two of the 3 characteristics of a particular cell.
Unfortunately MS only allows upto 10 or so words per suggestion which is a
little skimpy if you ask me. Maybe what I'm asking violates some law of
physics. Maybe the programming language necessary for accomplishment of my
idea has not been invented.

"Gord Dibben" wrote:

F5SpecialConstants. Uncheck/check what you want to delete and OK

EditClear Contents.


Gord Dibben MS Excel MVP

On Mon, 23 Jan 2006 10:36:04 -0800, "MrQuick"
wrote:

Is it possible to delete a cell's VALUE without deleting (or "Clearing") the
cell's formula?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc





  #6   Report Post  
Old January 25th 06, 02:30 AM posted to microsoft.public.excel.misc
Mark Lincoln
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone.

I don't understand what purpose such a thing would serve. If I don't
want a value in a cell, I do as Dave has suggested by writing my
formula to return nothing if the cell should look blank. Or delete the
formula.

If you don't want to see the value returned by a formula, I suppose you
could use the same color for the cell text and cell background.

  #7   Report Post  
Old January 25th 06, 07:01 PM posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone

1. Select all cells first.

2. F5SpecialConstants where you will have the four options to check or
uncheck.

Usually you would uncheck Text because you want to keep Titles etc.

3. Hit OK which will select cells with data from which your formulas derive
their values.

EditDelete and your data will be cleared, leaving the formulas intact but with
no values.

Experiment a bit until you get the correct combination.

Always best to do this on a copy of the sheet.


Gord

On Tue, 24 Jan 2006 16:40:03 -0800, "MrQuick"
wrote:

~ In the "Go To Special" dialog box, I clicked the empty circle w/"Constants"
next to it.
~ "Formulas" remained unselected.
~ The 4 boxes sub to "Formulas" became operative and were all checked: they
are... Numbers/Text/Logicals/Errors.
~ What boxes do I UNCHECK so as to allow me to delete a cell's value but
leave the formula alone after "OK" and then clicking EditClear Contents
~ Also, when should I select the cell to be operated on? Before hitting
F5...? Before EditClear Contents?

PS: My idea is for the "Edit" menu have a "Delete Special" or something like
that so users can decide do delete everything (values, formula, formatting)
or any one or two of the 3 characteristics of a particular cell.
Unfortunately MS only allows upto 10 or so words per suggestion which is a
little skimpy if you ask me. Maybe what I'm asking violates some law of
physics. Maybe the programming language necessary for accomplishment of my
idea has not been invented.

"Gord Dibben" wrote:

F5SpecialConstants. Uncheck/check what you want to delete and OK

EditClear Contents.


Gord Dibben MS Excel MVP

On Mon, 23 Jan 2006 10:36:04 -0800, "MrQuick"
wrote:

Is it possible to delete a cell's VALUE without deleting (or "Clearing") the
cell's formula?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc




  #8   Report Post  
Old January 25th 06, 11:28 PM posted to microsoft.public.excel.misc
Mark Lincoln
 
Posts: n/a
Default Excel should allow delete cell's value but leave formula alone

To be more precise, the formulae are left intact but their cells will
display whatever values are derived by operating on empty cells. This
leads to the possibility of the cells showing zeros (and zero is a
value) or error messages such as '#DIV/0!' or '#VALUE!'.



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
In Excel how do I delete or change the name of a range of cells? Naming and Deleting a Range Name Excel Discussion (Misc queries) 4 September 21st 08 11:00 PM
how do you delete cells when they are connected with a formula Rere New Users to Excel 10 December 28th 05 10:28 PM
Excel Formula to determine if cells contain certain data C Anderson Excel Worksheet Functions 2 September 1st 05 09:27 AM
EXCEL FORMULA RETURN X IF VALUE IN 4 CELLS HAS VALUE RERAA Excel Worksheet Functions 3 August 10th 05 10:12 PM
Excel - formula to calculate colored fill cells within a range wi. MA Excel Worksheet Functions 1 January 7th 05 05:06 PM


All times are GMT +1. The time now is 06:33 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017