Erasing contents and leaving the formula

Octavio · External Since: Aug 08, 2007 Posts: 2

What is the way to leave the formula (sum, etc.) intact after erasing the
contents inside? Where I do set this?
Thanks in advance.

Mike H · External Since: May 24, 2006 Posts: 862

Hi,

An example of what you are trying to do would help but if I understand
correctly you might try copying the cell and then 'paste special' 'paste
values'.

The will replace the formula (=a1+a2) with the result of that formula.

Mike

"Octavio" wrote:

> What is the way to leave the formula (sum, etc.) intact after erasing the
> contents inside? Where I do set this?
> Thanks in advance.
>
>

Octavio · External Since: Aug 08, 2007 Posts: 2

Thanks for your repply.
What I mean is, this is an example:
I have a time sheet for one week with the timing spent on different
projects each day of that week in rows and columns with the "total"
converging on the lower right hand corner of the page.
Then I want to reuse the same form for the following week, so I erase all
the numbers for the previous week, but I want to keep the sum formulas so I
would not have to recreate these again.
Right now I erase the formulas when I erase the numbers, so I assume that
there is a setting somewhere that will allow me to erase the numbers without
erasing the formulas.
Please advise if there is one.

"Mike H" <MikeH RemoveThis @discussions.microsoft.com> wrote in message
news:EDF708CD-292E-476B-B072-C37B438EC9CB@microsoft.com...
> Hi,
>
> An example of what you are trying to do would help but if I understand
> correctly you might try copying the cell and then 'paste special' 'paste
> values'.
>
> The will replace the formula (=a1+a2) with the result of that formula.
>
> Mike
>
> "Octavio" wrote:
>
>> What is the way to leave the formula (sum, etc.) intact after erasing the
>> contents inside? Where I do set this?
>> Thanks in advance.
>>
>>

JLatham · External Since: Sep 19, 2006 Posts: 1511

How are you erasing the numbers/formulas now?

There's no quick way to tell Excel to only deal with the numbers entered by
the end user. See Max's reply below for more on that. If you are selecting
by hand, simply do not select the cells with the formulas in them before
hitting the [Del] key.

But there is a 'work around' using the idea that Max did come up with.
Where you have text/labels that you don't want erased, change those to
formulas and then use his Edit | Goto | Special [Constants] method.

Example: You have a cell that you typed EMPLOYEE NAME into and of course
you don't want that erased. Change that to ="EMPLOYEE NAME" and it becomes a
formula instead of a constant and would not be selected using Max's
suggestion.

If you're doing this copy/cleanup with a VBA macro, then it can be altered
to just clear out the specific areas you need cleared out, leaving formulas
behind. Start down that road by posting the code here, if it exists.

"Octavio" wrote:

> Thanks for your repply.
> What I mean is, this is an example:
> I have a time sheet for one week with the timing spent on different
> projects each day of that week in rows and columns with the "total"
> converging on the lower right hand corner of the page.
> Then I want to reuse the same form for the following week, so I erase all
> the numbers for the previous week, but I want to keep the sum formulas so I
> would not have to recreate these again.
> Right now I erase the formulas when I erase the numbers, so I assume that
> there is a setting somewhere that will allow me to erase the numbers without
> erasing the formulas.
> Please advise if there is one.
>
>
> "Mike H" <MikeH.TakeThisOut@discussions.microsoft.com> wrote in message
> news:EDF708CD-292E-476B-B072-C37B438EC9CB@microsoft.com...
> > Hi,
> >
> > An example of what you are trying to do would help but if I understand
> > correctly you might try copying the cell and then 'paste special' 'paste
> > values'.
> >
> > The will replace the formula (=a1+a2) with the result of that formula.
> >
> > Mike
> >
> > "Octavio" wrote:
> >
> >> What is the way to leave the formula (sum, etc.) intact after erasing the
> >> contents inside? Where I do set this?
> >> Thanks in advance.
> >>
> >>
>
>

gls858 · External Since: Jan 31, 2005 Posts: 282

Octavio wrote:
> What is the way to leave the formula (sum, etc.) intact after erasing
> the contents inside? Where I do set this?
> Thanks in advance.

Instead of filling in your blank form copy it to another
worksheet and then fill it in leaving your original intact
to use the next time. A similar process would be to do a
Save As on the original thus creating a new workbook and
then use it.

gls858

Gord Dibben · External Since: Feb 23, 2004 Posts: 8800

I don't understand this Jerry.

>You have a cell that you typed EMPLOYEE NAME into and of course
>you don't want that erased. Change that to ="EMPLOYEE NAME" and it becomes a
>formula instead of a constant and would not be selected using Max's
>suggestion.

When F5>Special>Constants just uncheck "Text" and EMPLOYEE NAME won't be
selected.

Gord

On Wed, 8 Aug 2007 05:28:04 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis)> wrote:

>How are you erasing the numbers/formulas now?
>
>There's no quick way to tell Excel to only deal with the numbers entered by
>the end user. See Max's reply below for more on that. If you are selecting
>by hand, simply do not select the cells with the formulas in them before
>hitting the [Del] key.
>
>But there is a 'work around' using the idea that Max did come up with.
>Where you have text/labels that you don't want erased, change those to
>formulas and then use his Edit | Goto | Special [Constants] method.
>
>Example: You have a cell that you typed EMPLOYEE NAME into and of course
>you don't want that erased. Change that to ="EMPLOYEE NAME" and it becomes a
>formula instead of a constant and would not be selected using Max's
>suggestion.
>
>If you're doing this copy/cleanup with a VBA macro, then it can be altered
>to just clear out the specific areas you need cleared out, leaving formulas
>behind. Start down that road by posting the code here, if it exists.
>
>"Octavio" wrote:
>
>> Thanks for your repply.
>> What I mean is, this is an example:
>> I have a time sheet for one week with the timing spent on different
>> projects each day of that week in rows and columns with the "total"
>> converging on the lower right hand corner of the page.
>> Then I want to reuse the same form for the following week, so I erase all
>> the numbers for the previous week, but I want to keep the sum formulas so I
>> would not have to recreate these again.
>> Right now I erase the formulas when I erase the numbers, so I assume that
>> there is a setting somewhere that will allow me to erase the numbers without
>> erasing the formulas.
>> Please advise if there is one.
>>
>>
>> "Mike H" <MikeH.RemoveThis@discussions.microsoft.com> wrote in message
>> news:EDF708CD-292E-476B-B072-C37B438EC9CB@microsoft.com...
>> > Hi,
>> >
>> > An example of what you are trying to do would help but if I understand
>> > correctly you might try copying the cell and then 'paste special' 'paste
>> > values'.
>> >
>> > The will replace the formula (=a1+a2) with the result of that formula.
>> >
>> > Mike
>> >
>> > "Octavio" wrote:
>> >
>> >> What is the way to leave the formula (sum, etc.) intact after erasing the
>> >> contents inside? Where I do set this?
>> >> Thanks in advance.
>> >>
>> >>
>>
>>

Max · External Since: Sep 08, 2003 Posts: 3297

Another angle .. maybe you mean selecting just the cells with constants and
clearing these, leaving formula cells alone ?

If so, you might want to try pressing F5 > Special > Check "Constants" > OK
This will select all non-formula** cells in the sheet at one go.
You could then clear with the Delete key.

**unfortunately this might include some row/col labels, etc
besides precedent cells.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Octavio" <inquirer DeleteThis @inquirer.com> wrote in message
news:O5ci%23tZ2HHA.1100@TK2MSFTNGP06.phx.gbl...
> What is the way to leave the formula (sum, etc.) intact after erasing the
> contents inside? Where I do set this?
> Thanks in advance.