Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA = #N/A
In Excel 2000, I have used a spreadsheet for over 9 months without issue. All of a sudden, the GETPIVOTDATA function returns #N/A for 75% of its use, working normally for the other 25%. The pivot table, and its underlying data, changes every day to the extent that another day's transactions appear at the right-hand side of the pivot data. The incremental label is a date; the analysis labels remain unchanged. Why has GETPIVOTDATA sudden gone inconsistently stupid and what is the solution? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA = #N/A
It's impossible to give you a correct answer without seeing the formula
you're referring to. However, be aware that the GETPIVOTDATA syntax is different under XL 2000 than it is under later versions, so if you have upgraded XL recently, that could be the issue. See here for an explanation of how the function works among the various XL versions: http://www.contextures.com/xlPivot06.html Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Martin James Thornhill" wrote: In Excel 2000, I have used a spreadsheet for over 9 months without issue. All of a sudden, the GETPIVOTDATA function returns #N/A for 75% of its use, working normally for the other 25%. The pivot table, and its underlying data, changes every day to the extent that another day's transactions appear at the right-hand side of the pivot data. The incremental label is a date; the analysis labels remain unchanged. Why has GETPIVOTDATA sudden gone inconsistently stupid and what is the solution? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
GETPIVOTDATA = #N/A
Thank you for the URL.
The problem nevertheless persists! One of the formulae that work is: =-GETPIVOTDATA('x.xls'!PIV,"GBP "&$A11&" "&TEXT(B$2,"dd/mm/yyyy")) One of the formulae that don't work is: =-GETPIVOTDATA('x.xls'!PIV,"GBP "&$A10&" "&TEXT(B$2,"dd/mm/yyyy")) whe PIV is the named range that includes the pivot table. The pivot table is - and always has been - within range. A10 and A11 are text strings that correspond (congruently) with row labels in the pivot table. B2 is a date, converted into the same format per the pivot table (done specifically to make it work). To emphasise: * this worked for months and has now decided to give up! * identical formaulae behave inconsistently! * aaagh! Re-typing the formula results in error, as does copy-paste from a working formula. The function and the pivot table sit on different worksheets in the same workbook. The version of Excel is 2000 and always has been. "Dave F" wrote: It's impossible to give you a correct answer without seeing the formula you're referring to. However, be aware that the GETPIVOTDATA syntax is different under XL 2000 than it is under later versions, so if you have upgraded XL recently, that could be the issue. See here for an explanation of how the function works among the various XL versions: http://www.contextures.com/xlPivot06.html Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Martin James Thornhill" wrote: In Excel 2000, I have used a spreadsheet for over 9 months without issue. All of a sudden, the GETPIVOTDATA function returns #N/A for 75% of its use, working normally for the other 25%. The pivot table, and its underlying data, changes every day to the extent that another day's transactions appear at the right-hand side of the pivot data. The incremental label is a date; the analysis labels remain unchanged. Why has GETPIVOTDATA sudden gone inconsistently stupid and what is the solution? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GETPIVOTDATA help | Excel Discussion (Misc queries) | |||
getpivotdata | Excel Discussion (Misc queries) | |||
getpivotdata | Excel Worksheet Functions | |||
=GETPIVOTDATA | Excel Discussion (Misc queries) | |||
GETPIVOTDATA | Excel Worksheet Functions |