How do I protect one single cell?

I have a spreadsheet where people are going to type in numbers, and
the final cell is a total, which of course I don't want people to
accidentally type over, as it will lose the formula behind it.

So...... I put some "data validation" behind that cell, to say only
allow entries of text length = 50 - a kind of "mock" condition.
Nobody'e ever gonna type in 50 chars. are they?! And sure enough,
typing in (eg) 854221 brings up the error box to stop them, just like
(eg) HHJSYT brings up the error box to stop them. I even unchecked the
ignore blank" box, so that entering (eg) "         " does the same.

So much for numbers, letters and spaces. HOWEVER, if someone goes into
the cell and presses delete, delete, delete (i.e. making a "null"
character), it allows this! Help! I can't think of any way round this.
Can anybody suggest one?

Thanks.
0
7/4/2008 3:30:56 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
339 Views

Similar Articles

[PageSpeed] 27

Hi,

Select all cells then
Format|Cells|Protection
Un-Check the locked tab

Then select the cell you want to protect then
Format|Cells|Protection
Check the locked tab

Protect the sheet with 
Tools|Protection|protect sheet and add a password if you want and your done.

Note this is protection against accidental deletion only and removing your 
protection even without knowing the password is easilly accomplished

Mike


"Neil Grafton" wrote:

> I have a spreadsheet where people are going to type in numbers, and
> the final cell is a total, which of course I don't want people to
> accidentally type over, as it will lose the formula behind it.
> 
> So...... I put some "data validation" behind that cell, to say only
> allow entries of text length = 50 - a kind of "mock" condition.
> Nobody'e ever gonna type in 50 chars. are they?! And sure enough,
> typing in (eg) 854221 brings up the error box to stop them, just like
> (eg) HHJSYT brings up the error box to stop them. I even unchecked the
> ignore blank" box, so that entering (eg) "         " does the same.
> 
> So much for numbers, letters and spaces. HOWEVER, if someone goes into
> the cell and presses delete, delete, delete (i.e. making a "null"
> character), it allows this! Help! I can't think of any way round this.
> Can anybody suggest one?
> 
> Thanks.
> 
0
MikeH (222)
7/4/2008 4:35:03 PM
CTRL + a(twice in 2003) to select all cells.

Format>Cells>Protection.  Unlock the cells and OK

Select the cell with the formula and Format it to Locked.

Now protect the worksheet under Tools>Protection>Protect Sheet.


Gord Dibben  MS Excel MVP

On Fri, 04 Jul 2008 15:30:56 GMT, neil.grafton@tivoli.net (Neil Grafton) wrote:

>I have a spreadsheet where people are going to type in numbers, and
>the final cell is a total, which of course I don't want people to
>accidentally type over, as it will lose the formula behind it.
>
>So...... I put some "data validation" behind that cell, to say only
>allow entries of text length = 50 - a kind of "mock" condition.
>Nobody'e ever gonna type in 50 chars. are they?! And sure enough,
>typing in (eg) 854221 brings up the error box to stop them, just like
>(eg) HHJSYT brings up the error box to stop them. I even unchecked the
>ignore blank" box, so that entering (eg) "         " does the same.
>
>So much for numbers, letters and spaces. HOWEVER, if someone goes into
>the cell and presses delete, delete, delete (i.e. making a "null"
>character), it allows this! Help! I can't think of any way round this.
>Can anybody suggest one?
>
>Thanks.

0
Gord
7/5/2008 2:21:06 PM
Thanks folks! I was aware of the cell locking  and sheet protection
business, but could never connect the two before, hence my trying the
data validation rigmarole. It seems I just needed someone to list the
steps in the right order!

Thanks again.
0
7/5/2008 8:29:45 PM
Reply:

Similar Artilces:

How to print single sided versus double sided?
How do you set up for one sided printing. Everything I print is double sided. That would depend on where you are sending the print job. If it is a copier then get up and goto the photo-copier andcheck the copier settings, if it is a printer then check the printer properties. "bobm217" wrote: > How do you set up for one sided printing. Everything I print is double sided. ...

Excel wants to create a page break in every cell!
After saving revisions in an Excel worksheet, I get a popup message that states "Margins do not fit page size". After clicking "ok" (because this is my only choice) a page break is automatically created in ever cell. At times(depending on the sizeof the work sheet) it can be over 100 pages with a page size 14% or smaller. Why does this happen, and how can I avoid it? I saw this suggestion in a different forum. Try closing excel. Clean up your windows temp folder Windows start button|Run type: %temp% and hit enter Clean all those files that you ca...

Superimpose one chart on another
How do I superimpose one chart onto another so that I can see all of the data in both charts? One is a scatter graph and the other is a line graph using different sets of data in each graph. Hi, Well you can superimpose two chart but most of us would suggest that you chart both sets of data on the xy scatter and then for the one you want to be a line, you select its series and choose Chart, Chart Type and change it to an xy scatter with a line. If you want to supperimpose to charts put one over the other and right click the plot area, choose Format Plot area, and on the Patterns tab ...

Can't open files on more than one computer
I created a template file of a Christmas card for My class, but when I tried to open the template on more that one computer at a time I received a error message. The error was telling me that the file was in use by another computer and would not open. I thought that was the reason I created the template file in the first place but I Guess I was wrong. Having told the children that we were going to make a Christmas card I needed to come up with a solution to this problem so I saved the card on a disk and went around the room and opened the file and to save time I made three copy’s o...

Open file with a Pivottable Protected
Hi, I have a Workbook(1) link to another workbook(2) to retrieve and update names. In the workbook(1) I have 6 Pivot tables related to the same Data source I have also GetPivotdata formula in another sheets. I add buttons so the user just click to move from one page to another updating the Pivot table and the getpivotdata at the same time. Everything works fine. The problem is when I try to open the file (What ever I select Update or not) I have a message like this one: "That command cannot be performed while a protected sheet contains another PivotTable report bases on the...

Workbook Protection #2
I have created a workbook with 5 different worksheets. In each worksheet I have hidden certain rows. I want to be able to protect the hidden rows so that they can not be unhidden. Is there a way to protect the workbook so the rows are hidden without doing each sheet individually? I tried to use the Protect Workbook feature but when I tested it did not work. any help would be appreciated. Thanks, John Not manually. You could have a macro that cycles through the worksheet and protects each of them, though. Option Explicit Sub protectAll() Dim wks As Worksheet For Eac...

how do I make the cell expand to fit my text as I type?
I merged some cells across the width of a spreadsheet and i can't get the cell to increase it's height as more text is typed in wraps around. Only way I know, is to move your cursor to between the row number of therow you want to adjust and the next row. The cursor changes shape into an up/down arrow. Click and drag till you see all. Double clicking will also not work "pnwwoman" wrote: > I merged some cells across the width of a spreadsheet and i can't get the > cell to increase it's height as more text is typed in wraps around. If cells are merged. ...

How do I copy a cell from one worksheet to another worksheet
Hi On sheet2 =Sheet1!A1 Regards Roger Govier skywriter wrote: ...

Deleting cells within a row
I am not exactly sure what has happened. I have a worksheet with a number of rows and function within the rows. I would like to delete part of a row F6:M6, and shift things up. Normally this is never a problem, however when I right-click and select Delete I only get the option to delete the entire row or column. Any idea what may have happened? Thanks, Pablo Select the actual headers and not just the data (i.e., select the columns H thru F and then right click and select Delete. HTH and makes sense. ...

Find Average of cells having formulas
How would I calculate the average of a series of cells having a formula where the result is >0? i.e. ((G5-E5) + (S5-Q5))/ Total events Or do I have to calculate the end result to a new cell prior to finding the average? Thanks for your help I am not fully understood your question, I supposed that the total event = number of row in the series =sum(if((g5:g20-e5:e20)+(s5:s20-q5:q20)>0,(g5:g20-e5:e20)+(s5:s20-q5:q20)))/count(s5:s20) this is an array formula, not need to type { } in your formula, press crtl+shift+Enter "Dale" <dale@nospam.com> ���g��l��s�D:%23fZPLP...

Split cells VBA
Hi everyone, I've been using the following (kindly supplied by Rick Rothstein) to split address items. Sub SplitCells() Dim X As Long Dim Z As Long Dim LastRow As Long Dim Sections() As String With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Sections = Split(.Cells(X, "A").Value, "/") For Z = 0 To UBound(Sections) ..Cells(X, Z + 2).Value = Sections(Z) Next Next End With End Sub I now have lots of single sheet wkbks which have named sheets. I've tried taking t...

Hiding Cells
How do you hide cells without having to hide the entire column or rows? Hi McNiwram! You can hide the contents by formatting the font the same color as the background. You can hide formulas in the formula bar by using: Format > Cells > Protection Place check against "Hidden" OK Note that this doesn't come into effect until the worksheet is protected using the Tools menu. -- -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "McNiwram&...

default cell format
Is there a way to change the default cell format? Most of the people I talked to prefer top/left justified and are constantly changing to this from the default. Hi Chris, One way would be to create templates book.xlt for new workbooks sheet.xlt for sheets added to exisiting workbooks Templates, book.xlt, sheet.xlt, and beyond http://www.mvps.org/dmcritchie/excel/template.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm ...

Force Hyperlinked cell to appear at top corner and sort problem
I have created a workbook with a main page where I have hyperlinks to many work sheets within the same workbook. Some hyperlinks goes to same worksheet to different cells. Problem is in such worksheets When I click on the first hyperlink the control (focus) goes to the top left corner, but when I click on 2nd or 3rd Hyperlink for the same worksheet, the control is going to a cell displayed at the bottom left corner instead of top left corner. Is there any way I can force the hyperlinked cell to be displayed at top left corner always When I sort the main sheet columns, I loose the Hyperlinks ...

Setting a condition for MORE THAN ONE attribute to list ANOTHER di
I have a charter table containing : charter_nbr (numeric-pk), ... a charter_leg table containing : charter_nbr(numeric), cl_leg_nbr(nemeric-pk), cl_etd(date), cl_atd(date),... each charter flight may have more than one leg. and each leg has ETD(estimated date+time departure) and a ATD (actual date+time departure) here is the question: I need to desplay the charter nbr of the charters that ALL of its legs have the same ETD and ATD. meaning that any charter_nbr that ALL of its legs have this condition will be listed. notice that, the condition must be true for ALL the legs...

Setting Hard Hyperlink in cell
How can I set the hard hyperlink in a cell or change it? I can do it mannually though Insert Hyperlink, but how can I do it via programming function? Thank you in advance for anything. Mike Hi Mike, If you want a hyperlink you can change you might want to consider using the HYPERLINK Worksheet Function. As far as changing the Inserted Hyperlink such as changing the prefix to another prefix: http://google.com/groups?as_umsgid=e6%23fCptFCHA.2280@tkmsftngp12 Other things you might want to do with Hyperlinks can be seen in http://www.mvps.org/dmcritchie/excel/bujildtoc.htm --- HTH, Dav...

Test cells for values if date matches
I have the following formula: =IF($AT$122:$BX$122=K$4,(IF(OR($AT$147:$BX$166="R"),"R",IF(OR($AT$147:$BX$166="Y"),"Y","G"))),"G") AT122 to BX122 contains a date (not all dates for a month are present) K4 contains the date I need to report on AT147 to BX166 contain the processing status by site for each date. I am trying to get the cell I am reporting in to display an "R" if any of the cells in the column that corresponds to the date that matches K4 contains an "R", if there is no "R", then I w...

compare two cells with non-numeric data
I need to compare two cells (A1 vs B1) that contain letter grades (that is, A, A-, B+, B, B-, etc.) I need to show if one cell (B1) has a higher grade than the cell to which it is being compared to (A1), is the same or has a lower grade. If this were the comparison of number data, then no problem. Any help would be appreciated. Thanks One thing you could do is to allocate a nominal value to each grade. For example, set up a column somewhere, eg in X1:X10 like this: A A- B+ B B- C+ C C- D E I don't know what grades you are actually using, but I'm sure you can see the idea of list...

Hide or Protect Headers
How do I prevent others from changing the format of the header and footer. I tried protecting the sheet but people are still able to edit the header and footer. Did you lock the cells you want to be protected? Select all cells to protect, format cells, protection tab, check locked. Then protect worksheet & apply password then save. "RLD" wrote: > How do I prevent others from changing the format of the header and footer. I > tried protecting the sheet but people are still able to edit the header and > footer. I believe this can only be done through...

Count cells
Why is that my answer to my formula will not update? It was working fine at first not it do not. I want to count text in each cell (eg. A5-A9). The formula I use is =COUNTA(A5:A9). My answer will be 5. When I delete a row the answer do not update. Why? Press F9 to do a manual recalculation. Did that work? Then you worksheet is set to manual recal Open Options (what version of Excel are you using?) and reset to Automatic. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Danni" <Danni@discussions.microsoft.com> wrote in messa...

Linking Cells Between Sheets
This very straight forward function no longer works for me. When I enter "=" the cell reference cell changes to "ACOSH" as opposed to "VLOOKUP" on other computers. This will not eference cells on other worksheets within the same spreadsheet and even worse, changes seamingly random cell values in existing spreadsheets, often creating circular references. Nobody seems able to help and a the moment I am considering re-installing office! ACOSH is one of the Trigonometric Functions Simply typing an "=" sign in a cell should not invoke VLOOKUP or any ...

Imbedding Cell into a Label
Hello. Does anyone know if (or how) it is possible to imbed a cell reference into a label. For example: A1: = 20% A10: = "You own (a1) of the fund" Result in A10: "You own 20% of the fund" I would greatly appreciate anyone's assistance. Thank you. In A10: =CONCATENATE("You own ",A1," of the fund") -- Regards, Dave "Raymond Gallegos" wrote: > Hello. Does anyone know if (or how) it is possible to imbed a cell > reference into a label. For example: > A1: = 20% > A10: = "You own (a1) of the fund" > &...

pasting linked excel cell and maintaining formatting no matter wha
I am linked pasting a data cell from Excel into Word. I am using the paste special, paste link: unformatted text I make adjustments to the font and font size in Word. I goto Excel, and I modify the text in the cell and go back to Word and it changes. How can I get the formatting to stay the same, no matter what? In Word, make sure to modify the *paragraph style* applied to text. That way you would prevent any unexpected changes when the OLE link is updated. -- Stefan Blom Microsoft Word MVP "JEB" wrote: > I am linked pasting a data cell from Exce...

Blank cells not to compute
I have cells that say if value is equal a certain points will be given if a tie 2 points are given by comparing the score in 2 cells. the problem the games have not been played and is already showing a tie so everybody is gotten their 2 points for the tie and 5 point for getting the score correct blank = blank. How do i do so the cell when they are blank do not compute =IF(COUNT(A$1:B$1)<>2,0,IF(AND(A2=A$1,B2=B$1),5,0)+IF(A2=B2,2,0)) -- David Biddulph "victorio" <victorio@discussions.microsoft.com> wrote in message news:A5EA003B-2329-457D-9A9D-9ACC7...

552 <FQDN>your message counts in one session of 198 exceeds maximum setting 20
Hello Folks, I get the error "552 <FQDN>your message counts in one session of 198 exceeds maximum setting 20" The original message was received at Mon, 2 May 2005 11:18:00 +0300 (AST) from ns1.piramide.nl [195.18.93.6] ----- The following addresses had permanent fatal errors ----- <xxx@xxx.com> ----- Transcript of session follows ----- .... while talking to [192.151.106.72]: >>> DATA <<< 552 momserver.riyadh.afg.com: your message counts in one session of 33 exceeds maximum setting 20 554 <xxx@xxx.com>... Service unavailable Any insights?? It seem...