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.
5/3/2010 11:19:01 PM
excel.worksheet.functions 4936 articles. 0 followers. Follow

3 Replies

Similar Articles

[PageSpeed] 7

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

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


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
5/3/2010 11:53:44 PM
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 
template is ready for the next round.

at least that is what i would do.

"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.
5/4/2010 4:01:01 AM


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.

5/4/2010 3:46:29 PM

Similar Artilces:

How to keep the macro running?
Hi All Is it possible to include a code to let the macro to run non stop? i yes, would excel crash if i activate another macro at the same time? Cheer -- Message posted from Only one macro can run at a time. If a macro is running, you can't do anything else with XL. Take a look at the OnTime function to have a macro repeat at fixed (or variable) time intervals: In article <>, hce <<>> wrote: > Hi All > > Is it possible t...

Protect: Formulas?
I'm protecting a sheet via VBA. There seem tb quite a few parms to control what gets allowed/disallowed, but I can't find anything that applies to formulas. I want the user to be able to put formulas behind some cells. Right now, with the sheet protected, the user can type data into cells, but the Sigma icon that allows formulas tb put behind them is disabled. Unprotecting the sheet enables the icon... but I want various things to remain protected. Is there a way to allow formulas, but still have Protected=True? -- PeteCresswell Pete, You have to have unlocked the cells the use...

changing formulas to values so that they will be recognized by Find and Replace
Hello, Here is my dilemma: I have a list of patient names in Column A on Sheet 1 that will b updated over time. I have linked Column A on Sheet 2 to Column A on Sheet 1 so that an changes made to the list on Sheet 1 will automatically show up on Shee 2. However, when I need to search for a patient name on Sheet 2, I canno use the "Find" feature because it is reading the formul (='Sheet1'!A1), and not the value (Patient Name). I thought about using a second column on Sheet 2 to convert th formulas to values, but this column is not automatically updated when new entry is...

keeping values
I am sure that this question has been asked and answered. I am creating a worksheet for my class. I have a value in cell b2( I want use this value thru out the sheet). Let say b2 = 10 b c d 3 5 6 8 4 3 7 1 I want to subtract each value from b2(10). How can I do this without doing it manually? On Thu, 1 Apr 2010 14:06:04 -0700, AFL-math <> wrote: >I am sure that this question has been asked and answered. I am creating a >worksheet for my class. I have a value in cell b2( I want use...

Formula (not result) appears in cell
Every now and again when I type a formula into a cell, the formula itself appears in the cell (including the starting = sign), rather than the result of the formula. Even when I delete it and re-enter it the same thing happens. Has anyone got any idea why, and how I stop it from happening? I think I found the answer myself, but thanks for your help. The problem seems to occur when the original cell is formatted for TEXT, and it seems Excel reads (for some reason) the formula as text. The answer is to reformat the cell to GENERAL, and then re-enter the formula. Thanks again, Leslie ...

formulas #6
Hello, Can you tell me how to copy formulas and paste to another location without them changing? Thanks Colin Hi you may use absolute references such as $A$1 instead of A1 -- Regards Frank Kabel Frankfurt, Germany "Colin McLure" <> schrieb im Newsbeitrag news:L6HAd.314$ > Hello, > Can you tell me how to copy formulas and paste to another location > without them changing? > > > Thanks > > Colin > Change to absolute by adding $ where desired. =$a$1*3 -- Don Guillet...

Keeping photos in line
Several questions about moving photos in columns: I have a two column page, a manuscript of 150 pages, and want photos t slide with the type as I insert text in previous pages. I use th format, inline, move with copy instruction. Sometimes it moves th photo over to the other column. I want all photos inserted from files to automatically be embedded, o inline. Is there a command for that? Is there a better way to do a caption for the photos, what we printer call a cutline, than use a new text box? I have to in line it too, an it sometimes wants to move one or two lines when I give the order...

Configuring the Deletion Service
Can someone please tell me where the deletion service is configured as far as when it runs, etc.. ? thanks a bunch. ...

How to add new formula in Excel?
I had already wrote a new function in VB, would I add the function into excel as a new formula? Hi see -- Regards Frank Kabel Frankfurt, Germany k K wrote: > I had already wrote a new function in VB, would I add the function > into excel as a new formula? ...

How do I keep my own formatting for a pivot-chart on refreshing
I have many pivotcharts which need to be continually refreshed to represent statistics from data within the workbook. I need to display these charts with my own colour scheme - however the charts are refreshed they revert back to the dodgy Excel default colours. To complicate matters further they are part of a shared workbook - so my macro I recorded to re-apply my colour scheme doesn't work. Can anyone help???? ...

Combining Worksheets and keeping them separate
I have a workbook that contains two worksheets. Worksheet one as information such as name, address, etc. Worksheet two has the same fields/columns of information, however the data itself is different. I need to keep these two lists separated, however, I also need to have them combined at times. Is it possible to create "Worksheet 3" that will automatically update when I add/modify records in Worksheet 1 or Worksheet 2? Thanks. Glenn, There is not an easy way to do that. Instead of three worksheets, use one worksheet (a single database). Then you have two choices. 1) Enter...

Shorten Formula
Can someone help me shorten this formula? I'm not sure if the mathamatics can be shortened by altering the formula or using a different formula to figure out the problem, but the path name makes it exceed the max amount of charachters. Changing the linking files path location is not an option. I know I can achieve it by putting different formulas in a couple of seperate cell's but I'm trying to get the final result by only using one cell, I know the path name can be shortened by using '[pn] instead, but the name of the spreadsheet changes every month, so I can't easily find...

How do I hide zero values but keep currency format in P.O. form?
I'm creating a purchase order for our company and am having trouble formatting some cells. I need a currency value in the Total section only if there is a value in the Quantity and Unit Price section. I formatted the Total cells to Currency. But if there are no values in Quantity or Unit Price, zero values appear in the Total cells. How do I format the total cells to only show price values when there are values in Quantity and Unit Price? When you format a cell as currency you get a custom format like this (depending on the otpions you chose)... $#,##0.00;[Red]$#,##0.00 Choose the ...

Returning a cell contents using "Find"
Hi hope you can help. I am using a spreadsheet which has 3 worksheets. Worksheet 1 is an introductory worksheet. Worksheet 2 has data entered by a user who has to send special "files" to different offices. Each row in the spreadsheet records a file ID, who it was sent to and when. It also displays the current location of the file, (data extracted from worksheet 3). Worksheet 3 is used by those people who receive the files to record when it was received, and their action, eg, the date they return it or who they send it on to, and when. This worksheet is formatted in the sam...

Contacts 'display as' keeps showing email address even when I dele
I'm using Outlook 2007 recently upgraded from Outlook 2003 contacts. I don't like email addresses showing in emails, so I have always written 'display as' with name only in Contacts. However, Outlook 2007 has added the email address in 'display as' for everyone. When I go in to delete the email address, SAVE and Close - and go back to check, nine times out of ten, the email address is back in there. Very frustrating. Is this a known problem? Is there a fix? I just want 'display as' to simply reflect what I put in there - not what Outlook decides...

Protect formula cells
Hello, I am using Excel 2003 and I am wondering if there is a easy way in protecting formula cells in my workbook. I have about 25 formula cells and it is all over the workbook. My users keep changes the formula cells, they suppose to enter data not changing formulas. I have about 20 workbooks to do. Is there an easy way or global way to protect formulas using a password on a sheet? I don't want to go cell by cell. Can anybody help me on this one? Your help will be appreciated, thank you in advance. Hi Alex The default property for all cells on a sheet is Locked. You w...

Money2004 keep crashing on startup
just install in my WinXP SP1, which has IE6.0 SP1 installed already. Money2004 Std keep crashing on startup after install. with error dialog of "restart Money - Send / don't send report" any idea ? Any further details in the error, such as whether it's an Invalid Page Fault or a module name? -- Glyn Simpson, Microsoft MVP - Money Check;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use or email especi...

Can't delete file in Outlook express outbox
I have a large email file in my outbox that won't send and I can' delete it -- PhoenixVP ----------------------------------------------------------------------- PhoenixVPC's Profile: View this thread: PhoenixVPC, you wrote on Fri, 26 Aug 2005 13:46:14 -0500: > I have a large email file in my outbox that won't send and I can't > delete it. Close Outlook Express and search for the file "Outbox.dbx" on your system. Delete it an restart OE. Best Regards Christian Goeller -...

Disk keeps filling up
Hello everyone... My Exchange server (Version 5.5) has a directory (Folder)... That keeps filling up with files and making my disk full. The folder name is: D:\EXCHSRVR\IMCDATA\IN\ARCHIVE I can't for the life of me figure out why... Can anyone help me? Just Thanks in advance Dale Allen CCNA, MCSE, CNE On Fri, 12 Aug 2005 09:50:24 -0400, "Dale" <> wrote: >Hello everyone... > >My Exchange server (Version 5.5) has a directory (Folder)... That keeps >filling up with files and making my disk f...

personal folders keep disappearing
Hi, This is the setup. When we create a personal folder and point the location of the pst file to the users my documents, which is really a redirected network location everything is great until the pc is rebooted. After a reboot their are no personal folders showing and users have to add a new personal folder and browse to their my documents to locate the pst file. The outlook profile was automatically created by running a script that pointed to an edited .prf file. Here is an extract from the prf file that may be the problem: [Service1] OverwriteExistingService=No UniqueService=Yes M...

keeping parts of a formula same
I need to ask a simple question which involves when draging the formula to the cells below to make copies. If I had a Fromula like say =A5/SUM(B5:B103)*B5 and I want to drag it to a couple of cells below making copies and saving my self some time. How to I prevent one part of the formula from changing. Because when I drag in down to the other cells I only want *B5 to change to *B6,*B7,*B8,*B9 etc. etc. so is there any way to do this? Put $ in front of any references you want to keep the same ie =$A$5/SUM($B$5:$B$103)*B5 You can also use this to keep partial refs constant eg $A1 whic...

need to edit/delete attendance trx in HR standalone
Clients who run HR as a standalone need to be able to edit or delete attendance transactions. With the new attendance funtionality, HR only users can't delete or edit a transactions, they have to enter another transaction if they need to make a correction. I have a ---------------- 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 an...

keeping font the same size
Every time I close a visio drawing and reopen it later to make updates, the font size keeps going from 12 back to 8 and it is annoying to keep changing. How can I keep it that the font stays at 12 when I re-open the drawing. I'd also like to do the same with the line weights. Visio 2003 is installed with latest updates. It is part of the office 2003 pro. package. Thanks. ...

Keeping macros
I've got lots of Macros on my Excel from Office 97. Now, I'm upgrading my computer and office suite to Office 2003. I have tons of macros that I use all the time. How can I export them to my new Excel? Thanks for the help. Don't know where your macro's are but, in my case, I record my macro's in a special workbook that I keep in the startup folder. When I went to Of2k I just copied the workbook to the startup folder on the new machine. Maybe something's different in Off2003(?). -- Regards; Rob ------------------------------------------------------------------------...

Complicated Formula to Distinguish, weather the License is Valid o
I am working on a Huge Date requested by the Management, but I stopped working due to the data requested is hard to finalized it. It really need a Genius to create a formulas; It’s more than one formulas, but I am sure that you can create it. You’re much better than me, and thank you in advance; Let’s say for Example; .. A B C D 1 Mr. MMM 01/01/2010 01/01/2011 ????? in Column (A), The employee Name in Column (B), The Driving license issue Date ...