Excel Sheet Lock Down

Is there a way to lock down, so to speak, an Excel sheet that has formulas housed inside hidden columns?  The sheet is used for time entry in a business and people keep deleting and cutting and pasting in rows which messes up the formulas.  The sheets are eventually introduced to an importing interface and if the cells do not contain the proper data, the process is toast.  How do I lock down the sheet?

Why can't you insert a row into a sheet containing formulas and have the formulas automatically formulate the data?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TracyVBA DeveloperCommented:
Click Tools --> Protection --> Protect Sheet.  Supply a password if desired, check options you want to allow and click OK

Answer 2:  Because that's not how it works unless you code it :-)
You can easily drag the formulas down to the new inserted row though.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You need to protect your sheet and cells so that user's can't insert or delete rows/columns.

Your formulas should update automatically when you insert rows/columns. Can you post a sample formula that is not updating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello CynSzcz,

Before you protect the sheet as Broomee describes, you need to select the cells that you want the users to be able to change, then click Format - Protection tab - untick Locked - OK.

Then protect the sheet.

With regards to your second question, if you click into your data table, then Data - Lists - Create List,
Excel will turn your data table into a special kind of list and whenever you add a new row or insert a row, any format or formula applied to the rest of the list will also apply to the new row.

cheers, teylyn
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

I can think of the following options:

1) The ranges referred to in the hidden formulas should never be changed.
In that case, broomee's suggestion is perfect : select "untouchable" cells, go to Menu Format->Cells, and select Locked in the Protection tab. Deselect Locked option for all other "changeable" cells. Then protect the worksheet. All locked cells will not allow any changes.

2) The user should be allowed to change the values in the ranges referred to in the hidden formulas.
In that case, you have two choices:

 2a) disallow cutting on specific ranges: copying & pasting does not mess up formulas, only cutting them does.
 2b) rewrite the formulas so that they always refer to the same areas, regardless of cuts were made to the area.
In both cases, you'll also need code that writes the formulas into newly inserted rows.

2b) is most efficient to code, so that is the one I used below.

First of all, you need to know how to write your hidden formula "VBA"style. Easiest way is to turn on MacroRecorder (Tools->Macro->Record New Macro). Then go to the instance of your hidden formula in the upper most row, press F2, and then enter. Stop the MacroRecorder. You'll get something looking like this:

Sub CodeFormula1()
' CodeFormula1 Macro
' Macro recorded 06/04/2010 by kittenwhisky

    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""STD"",RC[-7],RC[-6])"
End Sub

Simplify this to:

Formula(1) = "=IF(RC[-5]=""STD"",RC[-7],RC[-6])"

That's the format we need for our VBA code.
Repeat this once for every hidden formula column, changing the result to Formula(2)= "..." , Formula(3)= "..." etc.

Use these formulas in the code below.

Note: to determine the rows that need hidden formulas, the macro goes from cell A2 to the last non-blank cell in column A. All rows in between will have their hidden formulas updated. Pls change as required.
Also, all hidden columns must be "named" individually, ie. do not abbreviate adjacent columns B&C to range("B:C"), it needs to say range("B:B,C:C") for the code to work.

Sub UpdateFormulas()
Dim DataRows As Range, FormulaColumns As Range, Formula() As String
Dim i As Double

'setup rows that contain data
'' goes from cells "A2" to last non-blank cell in column A
'' change as appropriate
Set DataRows = Range("A2")
Set DataRows = Range(DataRows, DataRows.EntireColumn.Cells(Rows.Count).End(xlUp))

'setup hidden columns
'' all hidden formula columns, each column separated by commas
''change as appropriate
Set FormulaColumns = Range("B:B,C:C,F:F")

'setup Formulas
'' set up a formula for each hidden formula column
ReDim Formula(1 To FormulaColumns.Areas.Count)
Formula(1) = "<< insert VBA-style formula found using Macro Recorder >>"
Formula(2) = "<< insert VBA-style formula found using Macro Recorder >>"
Formula(3) = "<< insert VBA-style formula found using Macro Recorder >>"

'insert formulas into each row
For i = 1 To UBound(Formula)
    Intersect(DataRows.EntireRow, FormulaColumns.Areas(i)) = Formula(i)
Next i

End Sub

Open in new window

CynSzczAuthor Commented:
For jppinto, this is the sheet you requested to look at.  It would be great if the formulas would override any new rows inserted into the sheet by individual timekeepers.  Everyone grabs a new sheet each month but some cut, paste, and insert rows which has messed up the importing process at the end of the month.  If I "lock down" the sheet, will the users still be able to use text replacement and their own macros?  This sheet is perfect in theory but not necessarily execution.  The sheet is used by timekeepers to record their time and then their assistant's enter it into a billing software which processes a .dat file for uploading to an electronic auditing house.  Only certain rows are mapped to the import template.

I am new to Excel and really appreciate your assistance.

Thanks,  Cyn-
It's my impression or the file that you uploaded has no formulas?!?
CynSzczAuthor Commented:
Column D contains a formula, as well as the columns between F and N.  If you put info. into the columns that appear and then unhide the others, you will see the formulas at work.  Perhaps I have added the them incorrectly.  I am new to this.  It is the hidden columns that are necessary for introduction into the import software later in the process.
I know it's an old question (the oldest) but I think attached worksheet does what you need.

Formula's are protected and any inserting or deleting of rows is prohibited.

This is achieved by a simple Sheet protection with only needed stuff unchecked.

Password is empty, just unprotect/protect it through Tools/Protect/Unprotect or Protect Sheet

jppinto deserves the points I think

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Broomee and I seem to be the only cleanup volunteers in Excel. Since neither of us gets to clean up questions in which we have posted, it makes sense that this Q has not been cleaned yet. I don't have much hope that the OP returns, though. If they have not reacted to all the nudges from EE, they probably never will
Unless the question asker returns, after all, he made the last comment and waited for some input, which he has now :-)
TracyVBA DeveloperCommented:
>>Broomee and I seem to be the only cleanup volunteers in Excel.

I guess we'll have to make sure not to participate in the same questions, so we can make sure to clean each others up! ;-)
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.