Pivot Table - problem with protected sheets

If you are OK with a little bit of VBA, then this might do the trick for you.

In the Worksheet Change event of the sheet which has your data in it (not the sheet with the pivot table on), enter the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet2").Unprotect Password:="mypassword"

Set MyPivotTable = Worksheets("Sheet2").Range("A1").PivotTable
MyPivotTable.RefreshTable

Worksheets("Sheet2").Protect Password:="mypassword"

End Sub

Replace "Sheet2" with the name of the sheet which contains your pivot table.
Replace "mypassword" with the password you are using to protect the sheet(s)
Replace the cell reference "A1" with a cell reference within your pivot table (I usually use top left cell ref)

Give it a go (on a backed up copy) and see how you go.  This method has worked for me using Excel 2003 & 2007.

There are two potential drawbacks to this though, the first is that every time you change something on the sheet with your data in it, the pivot table is updated - whether or not anything has actually changed within the pivot table, depending on how much data you have this might cause a slight speed problem.  The second is that any user could have a look at the above code and see the password

You can protect the VBA Project with a password so a casual attempt to view would not be able to see the password. To do this: In the VBA window Tools-VBA Project Properties - Protection tab - Lock project for viewing and enter password.

Having said that, Excel passwords (sheet and VBA alike) are so easy to break that they should only be viewed as a method to stop accidental changes and the novice Excel user. So I wouldn't worry too much about it being possible to see the password.

A variation on the solution above would be to set a Worksheet Activate Event on the Pivot table sheet which unprotects the data page and refreshes the table. This would mean the Pivot table is updated every time the sheet is visited but you could work on the data without refreshing. This may help any speed issues.