Pivot tables do not refresh contents when data is deleted an

The is a Refresh button that updates the data. Look on the Pivot Table toolbar. Right click the toolbar area and select Pivot Table. The button looks like an exclamation mark.

Try That.

HTH
:guitar:

If your headings are always the same you should be using a dynamic named range. Heres the drill.

DYNAMIC NAMED RANGE

[1] Activate Insert / Name / Define,
[2] Enter “EndRow” (Without the quotes) as name in the Names in the Workbook box,
[3] Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,x!$A:$A)

Note. Replace x with the sheetname where the database is. ]

Example: If the name of the sheet is INPUT
=MATCH(9.99999999999999E+307,INPUT!$A:$A)

If you don’t have numbers (amounts) in column $A:$A then change that reference to a column that does.

Example:
=MATCH(9.99999999999999E+307,INPUT!$C:$C)

[4] Activate Add,
[5] Enter “Database” (Without the quotes) as name in the Names in Workbook box,
(Just type over what is there)
[6] Enter as formula in the Refers to box:
(Again type over or alter what is there)

=OFFSET(INPUT!$A$1,0,0,EndRow,5)
(5 indicates the number of columns in the table. Alter to suit.)

[ Note. Replace 'INPUT' with the sheetname where your database is. ]

If your database does not start in row one change the formula.
For example if your database starts in row 10 then subtract 9 from the formula and change the first part to reflect where the top left corner of the database is.

Example
=OFFSET(INPUT!$A$10,0,0,EndRow-9,5) (Again change the sheet name.)

Remember the database starts in the row where the headings are.

[7] Activate OK.

Now, you're ready to use the dynamic range name, Database in your workbook. It will expand or shrink automatically along with deletions from or the additions to the data area. When used with a Pivot Table it will update automatically upon Refresh Data.

NOTE: You must create the Pivot table AFTER you create the Dynamic Range.
:yes:

Regarding your first problem see the attached workbook. It seems to work for me.

So it does, but if you check on the Counties drop down it has updated.:puzzled: I guess this is just another of excel's puzzles. Sorry for the delay responding. Been out of town.:smash:

The only sure way that I know of is to re-create the pivot table but that negates the value of the dynamic range. Looks like more investigation is warranted. Maybe someone else has an idea for a fix. By the way you did refresh the table didn't you?

Just a thought. Perhaps use "Clear Contents" rather than "Delete" would alleviate the problem.

Hi,

It is a "feature" of pivot tables to retain the classings. The usual response is that you have to re-create the pivot table. There is, however, a quicker work-around:

1) in the source data, rename the field that has changed. hit refresh
2) change it back to the original name, hit refresh.
3) pull the field back into te pivot table.

...not elegant, but qicker than building the whole thing again.

paddy

Follow the thread below for more help.

http://ozgrid.com/forum/viewthread.php?tid=1895&page=2

Thanks,
Rennie

Re: Pivot tables do not refresh contents when data is deleted an

Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables


'in Excel 2002 and later versions
'If unused items already exist,
  'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh
  Next pt
Next ws


End Sub     


Programmatically Clear Old Items -- Excel 97/Excel 2000




Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem


On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.RefreshTable
    pt.ManualUpdate = True
    For Each pf In pt.VisibleFields
      If pf.Name <> "Data" Then
        For Each pi In pf.PivotItems
          If pi.RecordCount = 0 And _
            Not pi.IsCalculated Then
            pi.Delete
          End If
        Next pi
      End If
    Next pf
    pt.ManualUpdate = False
    pt.RefreshTable
  Next pt
Next ws


End Sub

Lee