Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getpivotdata doesn't like certain numbers??
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! -- TerryJ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getpivotdata doesn't like certain numbers??
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---- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "TerryJ" wrote: 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! -- TerryJ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getpivotdata doesn't like certain numbers??
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. -- TerryJ "ryguy7272" wrote: 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---- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "TerryJ" wrote: 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! -- TerryJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=GETPIVOTDATA | Excel Discussion (Misc queries) | |||
GETPIVOTDATA | Excel Worksheet Functions | |||
GETPIVOTDATA help | Excel Discussion (Misc queries) | |||
getpivotdata | Excel Discussion (Misc queries) | |||
getpivotdata | Excel Worksheet Functions |