keep formula delete contents

[carspjcars]

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]

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,"")

 

[FSt1]

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

 

[Gord Dibben]

F5>Special>Constants>OK

Edit>Delete

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


Gord Dibben MS Excel MVP