1. You can use programming to automatically update the pivot table when
a change is made to the data sheet. For example, paste code similar to
the following on the data sheet module, where Sheet4 contains the pivot
table:

'===================
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet4").PivotTables(1).PivotCache.Refresh
End Sub
'======================

There are instructions here for inserting the code:

http://www.contextures.com/xlvba01.html#Worksheet

2. Add another copy of the Amount field to the data area of the pivot
table.
Right-click on the field heading, and choose Field Settings
For 'Summarize by', choose Average
Click OK



nc wrote:
Dear Debra

I posted this message but no one answered, I thought you might have the
answer. Please help.

1. How would I use macro to automatically update a pivot table when I have
made changes to the database sheet?

2. I have the following database,

Staff ID Dept Amount
AH100 AH 50
AH101 AH 100
LC100 LC 200

Is it possible to have a calculated field with the amount divided by the
number of staff in each department?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html