Ask Your Question

Getting #REF with Getpivotdata when asking for Row/Column figure

asked 2018-02-27 17:24:35 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 ?

edit retag flag offensive close merge delete



Without see a sample file, it's near to impossible to know what the issue is.

m.a.riosv gravatar imagem.a.riosv ( 2018-02-28 01:14:48 +0200 )edit

@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.

Jim K gravatar imageJim K ( 2018-02-28 04:16:53 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-03-01 16:24:33 +0200

Jim K gravatar image

updated 2018-03-01 16:29:40 +0200

The problem is not related to the amount of information. Rather, there is a great deal of inconsistency in upper/lower case.

  1. Delete the existing pivot table.
  2. Modify all GETPIVOTDATA formulas to use upper case consistently, for example, Received From ID and never Received from ID.
  3. Create the pivot table again.

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.

edit flag offensive delete link more


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

ChristopherThomas gravatar imageChristopherThomas ( 2018-03-01 22:29:38 +0200 )edit

answered 2018-02-28 23:54:23 +0200

ChristopherThomas gravatar image

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 ?C:\fakepath\test_getpivot.ods

edit flag offensive delete link more


This question was not well put together, but the spreadsheet attachment is well organized and illustrates the problem clearly.

Jim K gravatar imageJim K ( 2018-03-01 16:28:36 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2018-02-27 17:24:35 +0200

Seen: 547 times

Last updated: Mar 01 '18