getpivotdata doesn't like certain numbers??

[TerryJ]

This is a wierd one that I have not found anywhere else.
In Excel 2007, I have a pivot tables that rolls up expenses by month to GL
codes. I use getpivotdata() to access the values for each GL code and month
then use them in other summary worksheets.

This works for all GL codes except 1910, 1915, 5624 and 5689. For Example:
=GETPIVOTDATA("Total Cost",$A$3,"GL_Code",5245,"Invoice_Month","June")
returns the correct value of 519.68
But:
=GETPIVOTDATA("Total Cost",$A$3,"GL_Code",1910,"Invoice_Month","June")
returns #REF!, even though a value for this code in June appears in the
pivot table.
Now here's a strange one ... If I change one of the source table expense
records to GL code 1909, then:
=GETPIVOTDATA("Total Cost",$A$3,"GL_Code",1909,"Invoice_Month","June")
returns the correct value of 7,793.00
Also:
=GETPIVOTDATA("Total Cost",$A$3,"GL_Code",1910-1,"Invoice_Month","June")
returns the correct value of 7,793.00

I can change the GL code for the 1910 (or any of the other 3 numbers) to
something else and that works. Only those specific numbers do not work.

Yes, I have checked to ensure that these values all appear formatted as
integers.

Has anyone seen anything like this before? Can you help please?

Thanks in advance!

 

[ryguy7272]

Maybe you have a space before or after the 1910. Try the trim function to
remove preceding and trailing spaces.

I suppose there is a small chance that you could have some hard returns in
there, or some other invisible 'data'. Try running either of these macros
(MAKE A BACKUP OF YOUR DATA FIRST):

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

Sub Remove_CR_LF2()
With Selection
..Replace What:=Chr(39), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(146) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(180), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


HTH,
Ryan----

 

[TerryJ]

Thanks for the macros. Unfortunately they did not work, nor does the trim
function.

They cells where the GL Code values are stored are formatted as number, no
decimals. They are part of an Excel table of expense invoices. For each
invoice, a GL code is assigned and an invoice date given. The pivot table
rolls up the expense invoices into a summary report by GL Code, by Month name
(June, July, etc.)

I Have tried re-creating the pivot table report from scratch - same problem.
If I go to one of the invoice records in the table and manually type the
value 1909 (or 1911) into the GL Cod field and then refresh the pivot, I now
see an entry for that code in that month. If I then use getpivotdata() to
return the value it works - for GL Code 1909 and 1911 but NOT for 1910!
Most frustrating!

So the 4 problem numbers noted so far: 1910, 1915, 5624 and 5689 contiue to
cause the #REF! error - presumably because the function can't "find" them in
the pivot table report, even though they are clearly visible.

BTW =GETPIVOTDATA("Total Cost",$A$4,"GL_Code",1910) returns #REF!
and
=GETPIVOTDATA("Total Cost",$A$4,"Invoice_Month","June") reurns the correct
sum for all expenses in June.