A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

keep formula delete contents



 
 
Thread Tools Display Modes
  #1  
Old May 4th, 2010, 12:19 AM posted to microsoft.public.excel.worksheet.functions
carspjcars
external usenet poster
 
Posts: 1
Default keep formula delete contents

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.
  #2  
Old May 4th, 2010, 12:53 AM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default keep formula delete contents

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
  #3  
Old May 4th, 2010, 05:01 AM posted to microsoft.public.excel.worksheet.functions
FSt1
external usenet poster
 
Posts: 2,788
Default keep formula delete contents

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.

  #4  
Old May 4th, 2010, 04:46 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default keep formula delete contents

F5SpecialConstantsOK

EditDelete

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
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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.