The data from which I obtain a pivot table is large. With the GETPIVOTDATA function, I can obtain a row total; a column total, but not an item within the table itself.
I set up a mock chart of data much smaller, then from it obtained a pivot table and I have no problem at all.
Is there a restriction on the size of the data from which a pivot table can be used with the GETPIVOTDATA function ?
Without see a sample file, it’s near to impossible to know what the issue is.
@ChristopherThomas: Please do not post as community wiki. Also, describe how to create an example that illustrates the problem, for example, “insert 50000 lines with aa in the first column and incrementing numbers in the second column.” See guidelines for asking.
I attach a ODS with two sheets showing a successful outcome and the other sheet which gives the #REF output when using GETPIVOTDATA to retrieve an amount within a specified row and column.
Can anyone solve this problem ? The successful dataset is smaller than the other. Is there a limit somewhere ?test_getpivot.ods
This question was not well put together, but the spreadsheet attachment is well organized and illustrates the problem clearly.
The problem is not related to the amount of information. Rather, there is a great deal of inconsistency in upper/lower case.
GETPIVOTDATA formulas to use upper case consistently, for example, Received From ID and never Received from ID.I added Sheet3 to show the corrected pivot table and formulas: test_getpivot-modified.ods
Note: Different letter cases sometimes seem to work, but not other times. This behavior looks like a bug, so you may want to consider reporting it.
Jim, Many thanks for your help I have noted your modified Test Getpivot spreadsheet.
Sorry but I didn’t understand your “please do not post as community wiki”. comment.Perhaps you meant I should have edited my original post which seems to be the only way of attaching a file. A possible bug is worth checking out. However many thanks and kind regards.
Chris