Missing Pivot Data in Pivot Field (PF)-Weird!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Qull666@hotmail.com - 11 May 2007 04:16 GMT

Missing Pivot Data in Pivot Page Field (PF)-Weird!

Dear All,

Does anyone have an idea of why this happens. It's not often but sometimes.

This will be the first time after almost a year now. (had it like less than
5 times over 5 year)

My database is intact with every column and row filled with data.

No. of Rows: 2,400++
No. of Columns: 60++

This is what is missing:

Pivot Page Field is a list of bill no. from the database Column A (the most
primary column)

Pivot Page Field - Inv-No:
a) SM-06-30983
b) SM-06-30984 <----- this is missing (not even in the list), WEIRD
c) SM-06-30985

The other Pivot Tables are working except for this one particular Pivot Table.

So I Troubleshoot---> Database (but didn't work)

1. I have gone through the database structure
2. Re-typed the whole thing.
3. Copy > Paste Special > Value
4. Used Macro (Debra D's Change Pivot Field to force it out)
5. Used SM-06-30983 information and change to SM-06-30984
6. Change 1 of the 6 SM-06-30985 data line to SM-06-30984
7. Refresh like more than a million times.
8. Searched the advance list (hide list)
9. Look at the forum for a solution.

I just don't know if I wanna cry or laugh!!!

The only way I know is to rebuilt the Pivot Table AGAIN!!!

I was thinking of not to coz this pivot table is an invoice document with
lots of stuff. (rebuild is the last option)

If anyone has an idea and experienced of how to trouble shoot and retrieve
the data, that is a great cause.

Thanks all!!!
I am on the XLS 2000.

Reply to this Message

Debra Dalgleish - 11 May 2007 17:14 GMT

Is the invoice number in the source data, in the range that's used for
the pivot table? Maybe it's in the last few rows, and isn't being included?

> Missing Pivot Data in Pivot Page Field (PF)-Weird!
>
[quoted text clipped - 46 lines]
> Thanks all!!!
> I am on the XLS 2000.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Qull666@hotmail.com - 16 May 2007 08:51 GMT

Dear Debra, Thanks for replying.

Even if I change the 1st row (A2) to that Invoice number, it would not
appear (tried that). Its one of those days when it happens. lol.

The range is OK coz I use the Dynamic Data Range. (thanks to you)
(before this I would range the whole column, 60 columns and 1mb bcomes 20mb
for just one Pivot Table)

The invoice number is the 1st column, that makes it the most primary column.

And I used this VBA, hoping to flush it out.: (got this through one of your
helps!)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = Worksheets("VR-9-1-Invoice Report").PivotTables(1)
Set pf = pt.PageFields("Inv-No")

If Target.Address = "$E$1" Then

Application.EnableEvents = False
For Each pi In pf.PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
pf.CurrentPage = pi.Value
Exit For
End If
Next pi
Application.EnableEvents = True
End If
End Sub

The other 2 would appear.

It would not be fair to get you to trouble shoot this, but if you ever found
the solution, maybe put it on the Contexture website. I visit the website
quite often.

Thank you.

> Is the invoice number in the source data, in the range that's used for
> the pivot table? Maybe it's in the last few rows, and isn't being included?
[quoted text clipped - 49 lines]
> > Thanks all!!!
> > I am on the XLS 2000.

Reply to this Message

Qull666@hotmail.com - 16 May 2007 08:55 GMT

Dear Debra D,

If you would like a copy of the file with this weird thing, just send me an
email to stanlee_wong@yahoo.com. Thank you.

Reply to this Message

Debra Dalgleish - 17 May 2007 05:29 GMT

The caption for that item has been overwritten somehow. It appears as an
empty label at the bottom of the list of items. You could use a macro to
reset the captions for the page fields:

'=======================
Sub ResetPageCaptions()
'retrieve original field names
'if captions have been typed into pt
Dim pt As PivotTable
Dim pi As PivotItem
On Error GoTo errHandler

Application.ScreenUpdating = False
For Each pt In ActiveSheet.PivotTables
pt.ManualUpdate = True

For Each pi In pt.PageFields(1).PivotItems
pi.Caption = pi.SourceName
Next pi

pt.RefreshTable
pt.ManualUpdate = False
Next pt

exitHandler:
Set pi = Nothing
Set pt = Nothing
Application.ScreenUpdating = True
Exit Sub

errHandler:
GoTo exitHandler

End Sub

'=============================

> Dear Debra, Thanks for replying.
>
[quoted text clipped - 91 lines]
>>>Thanks all!!!
>>>I am on the XLS 2000.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Qull666@hotmail.com - 17 May 2007 06:29 GMT

Dear Debra,

It works. Thank you so much.

> The caption for that item has been overwritten somehow. It appears as an
> empty label at the bottom of the list of items. You could use a macro to
[quoted text clipped - 128 lines]
> >>>Thanks all!!!
> >>>I am on the XLS 2000.

Reply to this Message