Lock in formulas

I have a worksheet that I use over-and-over again. It has several columns of 
data, and some of the columns have long and hard to remember formulas to 
total the columns. I usually delete the numbers in the columns after I print 
out the worksheet, leaving the formulas intact at the bottom of the columns. 
Being human, I occasionally goof and delete the sum formulas too. Is there a 
way that I can lock the formula cell at the bottom of the column so that I 
don't accidentally delete it?

Thanks in advance,
Malcolm
0
Utf
5/24/2010 4:54:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1478 Views

Similar Articles

[PageSpeed] 26

A two step process
a) select the cell that you DO want the user to be able to change; using the 
Format dialog 'unlock' these cells
b) now protect the worksheet (in your case |I would not use a password)

If step (a) is too long winded: select all cells by clicking the place where 
the column headers intersect with the row headers and unlock all cells; Now 
selectively lock the few cell with formulas.
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

REMEMBER: Microsoft is closing the newsgroups; We will all meet again at
http://answers.microsoft.com/en-us/office/default.aspx#tab=4

"Malcolm" <Malcolm@discussions.microsoft.com> wrote in message 
news:05F793F9-55F7-4BB9-8DE0-C38DFFFAF582@microsoft.com...
> I have a worksheet that I use over-and-over again. It has several columns 
> of
> data, and some of the columns have long and hard to remember formulas to
> total the columns. I usually delete the numbers in the columns after I 
> print
> out the worksheet, leaving the formulas intact at the bottom of the 
> columns.
> Being human, I occasionally goof and delete the sum formulas too. Is there 
> a
> way that I can lock the formula cell at the bottom of the column so that I
> don't accidentally delete it?
>
> Thanks in advance,
> Malcolm 

0
Bernard
5/24/2010 5:21:45 PM
Bernard, Hi,
Thanks dude, that worked great.
Malcolm

"Bernard Liengme" wrote:

> A two step process
> a) select the cell that you DO want the user to be able to change; using the 
> Format dialog 'unlock' these cells
> b) now protect the worksheet (in your case |I would not use a password)
> 
> If step (a) is too long winded: select all cells by clicking the place where 
> the column headers intersect with the row headers and unlock all cells; Now 
> selectively lock the few cell with formulas.
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> people.stfx.ca/bliengme
> email address: remove uppercase characters
> 
> REMEMBER: Microsoft is closing the newsgroups; We will all meet again at
> http://answers.microsoft.com/en-us/office/default.aspx#tab=4
> 
> "Malcolm" <Malcolm@discussions.microsoft.com> wrote in message 
> news:05F793F9-55F7-4BB9-8DE0-C38DFFFAF582@microsoft.com...
> > I have a worksheet that I use over-and-over again. It has several columns 
> > of
> > data, and some of the columns have long and hard to remember formulas to
> > total the columns. I usually delete the numbers in the columns after I 
> > print
> > out the worksheet, leaving the formulas intact at the bottom of the 
> > columns.
> > Being human, I occasionally goof and delete the sum formulas too. Is there 
> > a
> > way that I can lock the formula cell at the bottom of the column so that I
> > don't accidentally delete it?
> >
> > Thanks in advance,
> > Malcolm 
> 
> .
> 
0
Utf
5/25/2010 12:16:01 AM
Reply:

Similar Artilces:

Extend formulae
In S2 I have =IF(R2="","",R2*0.034+0.2) extended down to S9. In T2 I have =IF(R2="","",R2-S2) extended down to T9. In U2 I have =T2. In U3 I have =IF(R2="","",U2+T3) extended down to U9. In other words a running total. When I enter an amount (�) in R10, cells S10 and T10 automatically update but not U10. Why won't the formula in U9 extend to U10? Sandy Hi Sandy Change the formula in U3 and down to: =IF(R3="","",U2+T3) Regards, Per "Sandy" <sandy_stephen@DELETEhotmail.com> skre...

locking up
This might be more of a GDI question, but since I'm in MFC, I'll ask: Would BitBlt-ing to the screen repeatedly tend to lock up a program, after alt+tabbing to another application? If so, why? This seems to be happening to me, and I want to get to the bottom of the problem. What I have is either a fade loop or a screen-size redraw, both based on a timer and bitblt. Quite often, after I leave the program to go to another app, I cannot return and must "end task". "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:%23YFL5Ks%23F...

Maintain Formatting with Formulas
I am writing a formula that references multiple cells as the possible source cell depending on certain parameters being met. My question is whether anyone knows how to maintain the formatting in the display cell? My possible source cells all contain different number formats (i.e. -1234.0 and ##00.0E-+00). Is there a way to write this into the formula, or is there a formatting option that allows you to keep the source cell's format? Any help would be great! Use the TEXT function to apply the same format to the values from the referenced cells as was used on those referen...

Date formulas needed
Three cells involved: E4: date entered manually by user F4: date next IPP due G4: date next Review due Need formulas for F4 and G4 to satisfy these conditions: 1) E4 is empty: F4, G4 = "" 2) E4 <= 21 days from today: F4 = E4 + 21 days, G4 = E4 + 90 days 3) Else: F4 = E4 + 6 months, G4 = E4 + 1 year -- David F4: =IF(E4="","",IF(E4<=TODAY()+21,TODAY()+21,DATE(YEAR(TODAY()),MONTH(TODAY())+ 6,DAY(TODAY())))) G4:=IF(E4="","",IF(E4<=TODAY()+21,TODAY()+90,DATE(YEAR(TODAY())+1,MONTH(TODA Y()),DAY(TODAY())))) be aware though adding 6 mont...

Desktop Alert Locks Computer
Greetings, I am seeing a little quirk on some of my desktops. It only happens occasionally, but it happens to different models of PC. Not all the time, but once in awhile, I have users that complain of their PC freezing. The only remedy is to do a hard reboot. I have noticed in these instances, that the desktop mail alert ( the preview that pops up by the system clock when you get a new mail) is showing when it froze. If I go into Outlook and turn off the desktop alert, then the PC freezing stops. So, I know that the Desktop Alert is the cause of the problem. Has anyone else ever seen this? Th...

Formula rounding incorrectly but not consistently
I am trying to calculate a discount on an exchange rate (rate x .75%) but when I do a manual calculation to verify the figures, some are rounding up or down without rhymn or reason. Example: Rate Manual Calc Excel Calc Should Be 1.8775 1.891581 1.8915 1.8916 (didn't round up) 1.2995 1.309246 1.3093 1.3092 (rounded up but shouldn't have) I have formatted the cells and verified the correct number of decimals. My exchange formula is: rate-(rate*discount) Should I have to enter a rounding formula? I did create one and it worked by itself but ...

paste a formula to a range of cells
hi all, Excel newbie here--If I have a range of cells in a row, all with the same formula (using relative cell addresses), and want to extend this row another ten cells to the right say, using the same formula, how do I do it. I've tried copying one cell, and pasting it these 10 cells (after I've highlighted them) but that doesn't work. thanks in advance Doug: That should work...Highlight the cell with the formula in it (NOT a blank cell), hit CTRL-C, then highlight those 10 cells on the Right, and hit CTRL-V...This should do it...Any questions, drop me an email... Steve &...

Lock formula to cell
I want a formula to lock to a cell so that when I add or delete a column the formula will always remain with say column H I know if the sheet is Password protected this will prevent this happening but that is no use when I have to unprotect the sheet to delete a column. Anyone got thoughts on this? regards Pat Hi Pat without using VBA you can't achieve this Frank Pat wrote: > I want a formula to lock to a cell so that when I add or delete a > column the formula will always remain with say column H > > I know if the sheet is Password protected this will prevent this >...

Copy and Paste a formula as Values in a fixed Range as a loop statement using VBA
I have 3 work sheets i.e. a "Summary" sheet, a "Phase1" and a "Phase2" sheet The Phase1 and Phase2 sheets are a result of certain parameters that are entered separately for each of the Phases that produces the results in them. All the 3 sheets have fixed ranges and they are A2:AC151, the dates are within the range C3:AC150 for each Phase. All the 3 sheets have the same structure something like this A B C D E SAP NO CostCenter Dec-03 Jan-04 Feb-04 422100 DTF-Office 60,000 186,000 174,000 422140 DTF-Office 40,000 124,000 116,000...

can I use 2 formulas in 1 cell and display both results
Hi there, I was wondering if its possible to have 2 formulas in one cell. Such as vlookup one value and then vlookup another value and display both results in one cell. Thanks You cane use: =VLOOKUP(...)&"-"&VLOOKUP(...) HTH -- AP "chris" <chris@discussions.microsoft.com> a �crit dans le message de news:30B112DA-D57C-4498-BBB0-CF75F883F6BC@microsoft.com... > Hi there, > > I was wondering if its possible to have 2 formulas in one cell. > > Such as vlookup one value and then vlookup another value and display both > results in one cell. &...

Formula R1C1 in VBA
Hi, I have the following code in VBA to give me some variance % results. Although the rows are always static the columns are not: ActiveCell.FormulaR1C1 = "=SUM(R[-34]C-R[-17]C)/R[-17]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C-R[-15]C)/R[-15]C" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "=SUM(R[-30]C-R[-13]C)/R[-13]C" What I want this to do is to start in row 45, in the column i am in (worked out prior to this in the macro) and put in a formula which basically in Excel would read: ...

formula sheet contains an existing name
I have a worksheet that when I try to copy a tab I keep getting 3 error messages that says "A formula sheet you want to move/copy contains the name "", which already exists on the destination worksheet. Do you want to use this version of the name?" If I click yes, I get 3 more of the same message with different doc names. If clicking no I get a box that says name conflict, name exists on destination sheet. Then it lists the old name: & asks for a new name. If I enter a new name, nothing happens. HELP!!! ...

locked excel File ??
Hi . I am working with my colleges with one Excel file in a shared file system. Sometime - when I open the file, there is the message "locked from College" - but he is definetly out of office and shut down his PC. We are using Excel 2003 + Excel 2007. What can I do?? THnks for help Susan ...

Locked out of database
Not sure what happened but get a message saying "database has been converted from a prior version of Access by using DAO compactdatabase and is now in a partially converted state." It says I should be able to import tables into a new data base but when I try this it just gives the same message about converting and the data base just doesn't open. Please help because lots at stake, have to do a report based on these tables so am waiting with anticipation!!!x Hi Pete, Usually this means you are opening an earlier version of Access in a new version. Make a copy of the data...

Outllook2007 to open my Outlook2002 Win7 says files locked.
I'm following 2002 to 2007 instructions step by step. When I point Outlook to my ..pst file, I get access restriction messages. How do I make sure these files can be accessed by Outlook 2007? Thanks. Which exact steps are these? From your Outlook 2002 machine, copy the pst-file to your Outlook 2007 machine and place it in a convenient location such as My Documents\Outlook\ In Outlook 2007 you can then reconnect to the pst-file via; File-> Open-> Outlook Data File... For details see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaai...

problem with index formula
The following fomula is contained in cell C16 and returns the correct values: =VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE) The following formula is contained in cell L18 and uses the result in C16 as a lookup value in the first "match": =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21:M21,-1)) My promlem is that when I try to match on cell C16 my index formula returns an error. However, when I change the index formula to reference another cell (let's say cell D16) and I type in the value shown in cell C16, the index formula works fine. I think my problem has ...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Conditional Formulas/Formatting
Here's my problem, based on my example: I've got a checkmark in cell A1, which I refer to in the Post Ref Column by "=A1". If there's a checkmark in the Post Ref column, then the entry is a balance (Either Debit or Credit). There should ALWAYS be TWO and ONLY TWO entries. One under the Debit OR Credit Column next to Post Ref. The other under Debit OR Credit under Balance at Top Right of the example image below. If the Balance is a Debit, there should be two Debit column entries. Vice Versa if the Balance is a Credit. The next row down, if the Balance above is a...

Rearrange a formula
How do I write a code to chart functions in this form After arrangement 2*x+xy = 4 >> y = (4-x)/x 3+xy +x = 0 >> y = -(3+x)/x So I just want the user to enter the function, then excel will plot it. I know how to plot the function after it has been rearranged, but what I don't know is how to rearrange the function to plot it Can anybody hep Thanx Both Stephen Bullen (http://oaltd.co.uk) and Tushar Mehta (http://tushar-mehta.com) have "Plot Formula" utilities on their web sites. I know Stephen's work...

Help for the formula
Dear All, Please i need the formula for colum D Status, it can display Pickcal or In or Out or Picked. Colum A Colum B Colum C Colum D Confirm Date scanin scanout Status Pickcal 17-03-10 11:38 In 17-03-10 11:38 17-03-10 11:49 Out 17-03-10 11:25 17-03-10 7:29 17-03-10 8:24 Picked 17-03-10 11:20 Picked Thans so much. Tr...

Lock autoshape to fixed position
Hello, Does anybody know if it is possible to keep an autoshape to a fixed position. I have a worksheet that has two panes, one is frozen i.e. a fixed header and scrollable rows. On the bottom of the page I want to put a button bar made of shape buttons. The problem is that when I scroll, the buttons scroll too! It would be nices if I can keep them in position. I know that is is possible to add a second window, but that does not look so well... Any ideas? Multiple fixed panes would be a great option! Change the alignment option to be absolute. Picture properties > Layout tab > Advan...

Formula reads date as number
I have a formula that reads a list of dates in cells K5 through K20 an compares these dates to 1 date in cell M3. Cell M3 shows a date bu when I run the evaluate formula auditing tool on the formula, M3 date shows as a number, thus the formula fails. Any hel -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49423 Dates are stored as number of days since 1-1-1900. So today (dec 16,2005) is stored as...

Money "lock-out" solution-thanks Kurt
Kurt Thanks for the idea. It worked. I unistalled money 2002 and the reinstalled using custom mode...up and running again! Thank -you What happens to all your data when you do that? Do you still have your data? I tried reinstalling, but no luck. I hate to uninstall, since I would lose all the data, right? Would appreciate info. Thanks, Alice >-----Original Message----- >Kurt > >Thanks for the idea. It worked. I unistalled money 2002 >and the reinstalled using custom mode...up and running >again! Thank -you >. > Your data is in a file *.mny. This is ind...

Formula error #6
I have a simple calculation C8-C14 that I use to figure my budget difference. It works in all the monthly columns I have set up except for one. I get a result of -1.42109E-13 What could I have done wrong????? Probably nothing. See here for the probable cause: http://cpearson.com/excel/rounding.htm Ignore the fact that the page says Excel 97, as you aill also see in the small print it actually applies to all versions of Excel. If you simply format to acctg or some numerical format and a few decimal places of precision, it will appear as 0. Regards Ken...........

Formula Question #20
I am setting up 3 different "fill in" worksheets for our sales department. I have all the pricing and informaiton on several sheets according to brand. What I would like find out is if there is some way I can have certain areas of the fill in sheets automatically filled in without the sales person having to go look up the information. Iv'e been going through the "if" formula, but can't get it to work. Thanks for any help Look at the VLOOKUP() function. There's a tutorial here: http://www.contextures.com/xlFunctions02.html -- Kind regards, Niek Otte...