Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel : Hiding/Locking Formulae
I have created a spreadsheet that has many formulae. The spreadsheet is to be
used by people who do not have a great understanding of Excel. I wish to protect the formulae so that others cannot overwrite these cells. Can anyone tell how to protect the cells without having to protect the whole workbook. Thanks. Allan |
#2
|
|||
|
|||
Unlock all cells except those containing a formula, or unlock cells to be
used for input by users, keep cells with formula locked, then protect the worksheet (possibly with a password): Tools/Protection/Worksheets Regards, Stefi €žAllan€ť ezt Ă*rta: I have created a spreadsheet that has many formulae. The spreadsheet is to be used by people who do not have a great understanding of Excel. I wish to protect the formulae so that others cannot overwrite these cells. Can anyone tell how to protect the cells without having to protect the whole workbook. Thanks. Allan |
#3
|
|||
|
|||
One way to lock all those formula cells and unlock all the others...
Select all the cells (ctrl-a (twice in xl2003)) format|Cells|Protection tab|uncheck locked With all the cells still selected edit|goto|special|check formulas then format|cells|protection tab|check locked (You may want to lock the cells that have instructions/headers/descriptions, too.) Then you have to protect the worksheet tools|protection|protect sheet But there are lots of things that can't be done on a protected worksheet. You may want to test a bit to see if you lose anything you need. Allan wrote: I have created a spreadsheet that has many formulae. The spreadsheet is to be used by people who do not have a great understanding of Excel. I wish to protect the formulae so that others cannot overwrite these cells. Can anyone tell how to protect the cells without having to protect the whole workbook. Thanks. Allan -- Dave Peterson |
#4
|
|||
|
|||
Dave
Something I'd like to note. Maybe old news but......... If the active cell is outside the used range, CTRL + a(once) selects all cells in sheet. If inside the used range CTRL + a selects the used range only and CTRL + a(twice) selects all cells on sheet. Gord On Fri, 30 Sep 2005 07:34:51 -0500, Dave Peterson wrote: Select all the cells (ctrl-a (twice in xl2003)) format|Cells|Protection tab|uncheck locked |
#5
|
|||
|
|||
I think it's more of what makes up that current region. If the current region
is less than 2 cells, then all the cells are selected. I put something in A1:L40, but cleared c11:I25 (just at random). I selected D17 (empty with empty cells surrounding it)--ctrl-A selected the whole sheet. I put something in D17 and with just d17 selected, I hit ctrl-A. I got the whole sheet. D17:D18 had data, I got that current region with D17 selected and then ctrl-A. ===== Now the fun part! I put something in D17 (still surrounded by empty cells). I selected D17:D18 (d17 the active cell) and hit ctrl-A. I got all the cells. I selected D16:D17 (d16 active and empty and d17 non-empty). I hit ctrl-a and the selection never changed. ===== So this screws up my warning! I don't like describing the "button" at the top of the row headers and to the left of the column headers--it's just too many words. Maybe: Select A1 and hit ctrl-a twice it may be overkill, but it always(?) works. (Ish!) Gord Dibben wrote: Dave Something I'd like to note. Maybe old news but......... If the active cell is outside the used range, CTRL + a(once) selects all cells in sheet. If inside the used range CTRL + a selects the used range only and CTRL + a(twice) selects all cells on sheet. Gord On Fri, 30 Sep 2005 07:34:51 -0500, Dave Peterson wrote: Select all the cells (ctrl-a (twice in xl2003)) format|Cells|Protection tab|uncheck locked -- Dave Peterson |
#6
|
|||
|
|||
I agree with statement
Maybe: Select A1 and hit ctrl-a twice it may be overkill, but it always(?) works. The rest of it is a mystery and I'm glad it was you and not myself doing all that experimenting. Gord On Fri, 30 Sep 2005 15:57:32 -0500, Dave Peterson wrote: I think it's more of what makes up that current region. If the current region is less than 2 cells, then all the cells are selected. I put something in A1:L40, but cleared c11:I25 (just at random). I selected D17 (empty with empty cells surrounding it)--ctrl-A selected the whole sheet. I put something in D17 and with just d17 selected, I hit ctrl-A. I got the whole sheet. D17:D18 had data, I got that current region with D17 selected and then ctrl-A. ===== Now the fun part! I put something in D17 (still surrounded by empty cells). I selected D17:D18 (d17 the active cell) and hit ctrl-A. I got all the cells. I selected D16:D17 (d16 active and empty and d17 non-empty). I hit ctrl-a and the selection never changed. ===== So this screws up my warning! I don't like describing the "button" at the top of the row headers and to the left of the column headers--it's just too many words. Maybe: Select A1 and hit ctrl-a twice it may be overkill, but it always(?) works. (Ish!) Gord Dibben wrote: Dave Something I'd like to note. Maybe old news but......... If the active cell is outside the used range, CTRL + a(once) selects all cells in sheet. If inside the used range CTRL + a selects the used range only and CTRL + a(twice) selects all cells on sheet. Gord On Fri, 30 Sep 2005 07:34:51 -0500, Dave Peterson wrote: Select all the cells (ctrl-a (twice in xl2003)) format|Cells|Protection tab|uncheck locked |
#7
|
|||
|
|||
I have now taken off my white coat!
Gord Dibben wrote: <<snipped The rest of it is a mystery and I'm glad it was you and not myself doing all that experimenting. Gord <<snipped |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulae have stoped working on an excel sheet | Excel Worksheet Functions | |||
Excel startup switches | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |