keep formula delete contents

  • Follow


I'm trying to set up a template in Excel. I wish to keep the formula but 
delete contents, so i can us the same sheet again.
0
Reply Utf 5/3/2010 11:19:01 PM

Every formula evaluates to something -- a string, a number, an error, ...

So you can't really delete the contents and still keep the formula.

But you could modify the formula so that it returns an empty string ("") and
that makes the cell look empty.

You could modify the formula to look at the other cells it needs and if those
are empty, then return that "" string.

For example, if I had a formula that just added 12 to the value in A1, I could
use:
=A1+12

But if I wanted to make the cell look empty until something is put into A1, I
could use one of these:

=if(a1="","",a1+12)
or
=if(isnumber(a1),a1+12,"")



carspjcars wrote:
> 
> I'm trying to set up a template in Excel. I wish to keep the formula but
> delete contents, so i can us the same sheet again.

-- 

Dave Peterson
0
Reply Dave 5/3/2010 11:53:44 PM

hi
do you mean that you want to keep the formula AND column headers(other 
headers too) but just delete the data that the fomulas key on??
if so.......
A macro might be best for this.
make a list of all the cells you want to delete from your template. 
this list would exclude cells that contained formulas or headers.
the macro would look something like this....

private sub DeleteJunk()
Range("B3:B20,C9:C20, D12,D14,E3:G3, E14, E16").ClearContents
end sub

I made up the ranges above so you would obviously replace all the cell 
references with your own from your list.
you would list the delete cells as ranges such as (for example) B3:B20 or 
E3:G3 plus all the individual cells like d12, D14 ect seperating each 
reference with a comma.
since it is a template, you could drop a command button on the sheet and 
attach the macro to the button.
this way, you fill in the template, save or print, then click the delete 
button. 
template is ready for the next round.

at least that is what i would do.
Regards
FSt1

"carspjcars" wrote:

> I'm trying to set up a template in Excel. I wish to keep the formula but 
> delete contents, so i can us the same sheet again.
0
Reply Utf 5/4/2010 4:01:01 AM

F5>Special>Constants>OK

Edit>Delete

Uncheck "Text" if you want to keep data like titles.


Gord Dibben  MS Excel MVP

On Mon, 3 May 2010 16:19:01 -0700, carspjcars
<carspjcars@discussions.microsoft.com> wrote:

>I'm trying to set up a template in Excel. I wish to keep the formula but 
>delete contents, so i can us the same sheet again.

0
Reply Gord 5/4/2010 3:46:29 PM

3 Replies
1844 Views

(page loaded in 0.058 seconds)


Reply: