Protect Excel formulas

[DanIT]

A user needs to protect some formulas. The spreadsheet has been set up for summarizing. When he protects the sheet so the formulas cannot be edited, he can no longer expand the sheet.

I also tried using data validation rules on the cells to prevent a user from entering something in that cell. However, that allows the user to delete the contents.

He needs to protect the formulas but allow expanding the summarized data. Any help would be greatly appreciated.

Thanks,
Dan

 

[DaleWatson123321]

Dan,

It's difficult to know for certain without seeing your actual file; however, what about the following...

Place your summary data on a SEPARATE sheet that is of course protected.

Have your summary formulas reference the SOURCE sheet - which is unprotected.

I hope this is workable. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

If you can use VBA, you can set this macro up for the worksheet:

Option Explicit
Dim LastRow As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  If Target.HasFormula Then
    If Target.Row >= LastRow Then
      Target.Offset(1, 0).Select
    Else
       Target.Offset(-1, 0).Select
    End If
  Else
    LastRow = Target.Row
  End If
End Sub

You can make it more specific to bypass only some formulas based on whatever criteria you need. For example, you might be able to put all of the formulas that need protection in the same range and only bypass if the target cell is in that range.