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

3 Replies
2652 Views

Similar Articles

[PageSpeed] 37

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
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
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
Gord
5/4/2010 3:46:29 PM
Reply:

Similar Artilces:

Outlook keeps prompting for logon..
User is on Outlook 2k3, latest service packs, updates, etc. Mailbox lives on Exchange 2007 (SP1). Everytime the user starts Outlook they're prompted to logon. I've disabled the logon prompt at startup option (and enabled it and then disabled it again), but it's still happening. I've tried it with cached mode on and cached mode off. I've tried it with offline mode enabled and disabled. I've tried deleting his profile and recreating it. I've tried switching to an RPC/HTTPS connection. Finally, i completely uninstalled Outlook 2k3 and installed Outlook 2k7. Not...

Formula to Value
Hi there I have just updated to from 2000 to 2003, and cannot find a toolbar button for formula to value. I can copy and paste special in the same cell, but this is mildly irritating. Am I being dumb? TGHCOGO --- Message posted from http://www.ExcelForum.com/ I don't think it has ever been there without help at least. Do as follows: click view>toolbars>customize, select commands, select edit in the left pane and scroll down until you see something that looks like an old time almanac date that you would tear off every day |12| it has a 12 in it, paste values is the name, now s...

how do i delete a (read only) file?
i am trying to delete some files that are (READ ONLY) anyone have a clue as to how? i am in microsoft excel. Open windows explorer. go to that folder right click on the file to delete select "delete" answer yes to the "are you sure prompt" (somethings don't need to be done in Excel.) Renee wrote: > > i am trying to delete some files that are (READ ONLY) anyone have a clue as > to how? i am in microsoft excel. -- Dave Peterson ...

Help Old addresses keep coming up
When I go to send an email to some people their old email addresses keep popping up together with their current ones. I do not have them listed in contacts or in an address book. It is a minor annoyance. I am using Outlook 2003. It is a minor annoyance. How do I delete these old addresses? Where they show up is where I am entering the e-mail address on the item being e-mailed. Thanks You did not specify how you were entering the addresses. Presumably you are using autocompletion and they are...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

Confirmation of receipt keeps trying to send and fails
Help! I received an email message that requested a confirmation that it had been received. I told it to send confirmation, but it failed when attemtping to send it and produced an error. I tried deleting the email message and everything in the draft items and Outbox, but every time I switch on Outlook the darn thing tries to send this email confirmation. It seems there is nothing I can do to stop it, even though I cannot see it! I have tried uninstalling Office 2003 and reinstalling it, but all of my settings are remembered as are all the old emails. I just cannot seem to get rid of it! ...

Formula error since 10.5.6 update ...
Hi, I have been working with a spreadsheet for years without any problem of this sort. Since my recent 10.5.6 upgrade a part of a formula does not return a correct result. My problem seems to be related to the incorrect result of this part of my formula: TEXTE($N$5;"dd-mm-yy"). In this example, N5 is equal to "31-10-09". This formula should return : 31-10-09 It returns "dd-10- yy". Funny, the "31" is replaced by "dd" and "09" by "yy", while the month number is correct. Any idea with this is all about ? ...

Formula Help 2 QUESTIONS
I am trying to do the following: QUESTION 1: I have a spreadsheet that contains hundreds of records and most if not all of them are duplicated. I have tried an advanced auto filter but it doesn't seem to be working correctly. Is there a formula I can use to only pull one record into a new spreadsheet ----------------------------------------------------------------------------------------------- QUESTION 2 I am trying to compare information in spreadsheet ONE to info in spreadsheet TWO and return data into spreadsheet THREE. All three spreadsheet are saved separetly, how would I wri...

Keep Lines Together; Keep with Next
I'm working in Vista. When I select lines and click on "Keep Lines Together," the lines do not stay together. They stay together if I also click "Keep with Next," but I'm working on a booklet in which the pages are 1/4 of a letter-sized page, and a lot of space (4 lines) is left at the bottom and wasted if I also click "Keep with Next." Why won't "Keep Lines Together" keep the lines together? The project is a small, bound country club booklet, with names, addresses, phones, etc. Each entry might be 2, 3, or 4 lines. Any h...

how to delete a few old journal
How can I delete a few old journal i have over 500 of them and i want to delere 6 of them of can i do it. ...

Regression Leverage Formula (Jerry W. Lewis or Mike Middleton) already have DFITS formula
I have been trying to figure out the leverage formula for a set of X Y values X Y 75 16 83 20 85 25 85 27 92 32 97 48 99 48 Using the data analysis add-in and adding residuals then running regression gives me Predicted Y, Residuals and Standard Residuals, If my data was in A2:B8 (X and Y) and C2 housed this formula =SLOPE($B $2:$B$8,$A$2:$A$8)*A2+INTERCEPT($B$2:$B$8,$A$2:$A$8) which gives me my predicted y and D2 housed B2-C2 which gives me my residuals then E2 housed =D2*D2 which gives me my residuals squared. What I am looking for in cell F2 is HI1(the weight...

why do my pictures keep disappearing?
Although I have checked the box in 'pictures' any form of graphic or word art that I have inserted into my publication keeps disappearing! If I click in the general direction of the insertion the bounding dots appear, but no picture Are you sure you have view, pictures, detailed display checked? If so try updating your graphics driver... -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "JG" <JG@discussions.microsoft.com> wrote in message news:57E101AC-A758-400A-9269-3B68B754CA0E@microsoft.com... > Altho...

Outlook 2003 keeps asking for password for POP3 account
I am running WinXP Pro and Office 2003. My Outlook 2003 keeps popping up intermittently a pop-up box requesting me to re-enter the POP3 account password. Now, there is nothing wrong with the password already entered (check box to save password), so all i am having to do is click OK, but it is really annoying. Does anyone know what i can do to stop Outlook from doing this? Thanks for your help. ...

Formula to count only positives in range
Hi am using Excel 2003. I am trying to add only the numbers that are in posotives in a particular range. for example. A1 = 10, A2 = 11, A3 = 7, A4= -6, A5= 8. How do I go about adding all the cells except A4 in a formula? Your assistance is greatly appreicated. thanks Stadinx One way =SUMIF(A1:A5,">0") -- Regards, Peo Sjoblom (No private emails please) "Stadinx" <Stadinx@discussions.microsoft.com> wrote in message news:212E2D37-5694-4073-93C9-4988B262AAC3@microsoft.com... > Hi > am using Excel 2003. > I am trying to add only the numbers that a...

Rounding Formula
Is there a function that allows you to round a value up to the nearest multiple of 5? I tried the round up function, but I don't think that is going to work. I need to take the value of a cell (eg. 241 needs to rounded up to 245) and round up to the nearest multiple of 5. Any advise would be gretaly appreciated! -- puppypants ------------------------------------------------------------------------ puppypants's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28654 View this thread: http://www.excelforum.com/showthread.php?threadid=483177 If you really mean ...

Blocking emails with HTML content
The level of spam I am receiving has recently gone through the roof - from 1-2 per day to between 25-40! I have set up junk mail rules and these do a fair job. However, a very large proportion of spam get contains HTML. I never receive messages from trusted sources with HTML in the message body, so my thinking is that if I can set up a rule to auto-delete all emails with HTML in the body I can get rid of 95% of it easily. However, I can't seem to find of way of doing it. Any ideas please (short of changing my email address!)? Thanks - Peter Peter, I had the same thought as you (blo...

how can I keep a tab in place?
I have workbooks with multiple worksheets. I would like to keep the summary tab in a fixed (first) position so that the user can easily, quickly find it without using the l<, <, >, >l (arrow) tabs. I use hyperlinks to get from the summary tab to the other tabs, and I know I can hyperlink tab back... but I'd rather not use the hyperlinks too much. Tools - Protection - Protect workbook (for structure) Or, create a custom button/macro that navigates to the summary sheet. -- Best Regards, Luke M "missy8892" <missy8892@discussions.microsoft.com>...

formula... PLEASE HELP
Hi, I have what I think is a formula...on Sheet 1 I have a total $ amount for items sold. I typed in L3+M2 (the L column is profit and M is the total..NOW..on Sheet 2 I have the same thing for different items, I would like to take sheet 2's M column and add it to sheet 1's. Is this possible? Thank you soooo much. Rosie =L3+M2+'Sheet2'!M2 as an example -- Regards, Peo Sjoblom "Roelamp" <Roelamp@discussions.microsoft.com> wrote in message news:91EA3B4A-EB37-47E9-A734-FA0736F9C9E8@microsoft.com... > Hi, > I have what I think is a formula...on >...

Help with altering a formula
I am using the following formula as part of my data source for a chart and it works perfectly. It sums column D, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column E, rows 2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab (AAT_Raw_Data) to dynamically get factored in. =SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AAT_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1)) ...

Delete Multiple duplicated Contacts
How do I delete multiple contacts in 1 go from my contacts list in Live Mail? And no the check boxes alongside dont work? Ken Use the normal Windows selection techniques... Ctrl-A to select all hold Ctrl and click on individual contacts click on a contact, hold the Shift key, and click on the end of a block of contacts -- Dave N. MS-MVP (Mail) Windows 7 Ultimate 64 http://explore.live.com/windows-live-mail http://download.live.com/wlmail "whatlink" wrote in message news:8D71D267-F43D-4BC8-A570-FADE039E9A1A@microsoft.com... How do I delete multiple co...

How do I keep the home tool bar on top
An hour ago the home tool bar stoped being displayed. I can restore it but every time i hit a key the tool bar disapers. How do I keep the tool bar on top while I am typing. You've minimized the Ribbon. Double-click any tab on the Ribbon to restore it. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "RobertTaylor" <RobertTaylor@discussions.microsoft.com> wrote in message news:081A95C4-76B9-47E2-A4E6-83A080233AD0@microsoft.com... > An hour ago the home tool bar stoped being displayed. I can rest...

Recover deleted report
Hi, I deleted the standard 'Quote' report that came with CRM. Our backup writes over itself each night so it is not on a backup tape. Is there anyway to retrieve that report from the install without reinstalling everything? If so, can you give specifics as the network person does not know how to do it and neither do I. We're new to CRM and would really appreciate any help with this. Thanks so much You may have a copy in the following folder on the CRM application server: [Drive where CRM was Installed]:\Program Files\Microsoft CRM\Reports\MSCRM -- MD "J2girl&qu...

FBI FORENSICS CAN FIND DELETED FILES xxxaaaaaaaaaaaasssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
begin 644 260dfrq1j.txt M66]U)W)E(&EN(%-E<FEO=7,@5')O=6)L92 M($ET)W,@82!0<F]V96X@1F%C M="$-"@T*1D))($9/4D5.4TE#4R!#04X@1DE.1"!$14Q%5$5$($9)3$53#0H- M"E1(15)%($%212!42$E.1U,@3TX@64]54B!#3TU0551%4@T*5$A!5"!93U4@ M5$A/54=(5"!93U4@1$5,151%1"X-"@T*(2$A(4)55"!42$59($%212!35$E, M3"!42$5212$A(0T*#0I#;&EC:R!H97)E(&9O<B!D971A:6QS(&]N('1H92!% M5DE$14Y#12!%3$E-24Y!5$]2#0H-"D-L96%R('EO=7(@8V]M<'5T97(@;V8@ M:6YC<FEM:6YA=&EN9R!F:6QE<PT*=&AA="!Y;W4@8V%N;F]T(&1E;&5T92X- M"@T*=W=W+G...

How to keep userform always on the top of all windows application
Hi All!! Is there some that when I execute an excel user form, it *remains o the top * of all the windows applications that are currently running until & unless I, manually, minimize or close it. Regards, Jat -- JAtz_DA_WA ----------------------------------------------------------------------- JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2668 View this thread: http://www.excelforum.com/showthread.php?threadid=40059 You may get an answer to your question--but it'll involve both making the userform always on top plus adding those minim...

Adding rows of data to 2 worksheets that are linked with formulas to eachother...
I have 2 worksheets. Sheet 1 contains address information for names of people we contact (including the names). Sheet 2 contains some of the contact information, including about 15 columns of dates that aren't included in sheet 1. I need to add in about 100 rows of new data that isn't included on either sheet. My problem is that the data that is the same on sheet 2 is linked to the cells in sheet 1. Rows in sheet2 read (=Sheet1!A2)(=Sheet1!B2)(=Sheet1!C2)(=Sheet1!G2),etc (they skip some colmns from Sheet 1) for every row. How do I add the extra 100 rows in without mixing up the d...