Forum Discussion

Tom_at_work's avatar
Tom_at_work
Copper Contributor
Oct 28, 2019
Solved

GET PIVOTDATA #REF! error

I am frustrated!  I have posted this question 2 times, but it never appears in my conversations on the community.  Trying again.

 

I want to use the GET PIVOTDATA function to reference data from within a Pivotable.  When I enter: =GETPIVOTDATA("Amount",'2019'!$R$4,"Fund","1100","Orgn","2002","Acct","5815"), I get the result of $15,589.15

 

I want the function to be dynamic and retrieve the information displayed in the Acct field stored in cell T8 in the Pivot Table.  When I change the function to =GETPIVOTDATA("Amount",'2019'!$R$4,"Fund","1100","Orgn","2002","Acct",T8) I get #REF!

 

What am I doing wrong?  How do I fix this to keep it dynamic?

 

Thank you in advance for your help,

Tom

 

  • Depends on what's in T8. If that value isn't in the pivottable #REF! is the result. Please note that you might need to convert the content of T8 to text:
    =GETPIVOTDATA("Amount",'2019'!$R$4,"Fund","1100","Orgn","2002","Acct",T8&"")

8 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    Tom_at_work 

    I believe it doesn't like the explicit reference calling out to T8 directly.  After you changed the formula did you change the pivot arrangement or toggle the filter?

     

    You may want to arrive at T8 without referencing it directly.  INDIRECT or OFFSET is worth a try.

    • Tom_at_work's avatar
      Tom_at_work
      Copper Contributor

      Patrick2788 

      Thanks Patrick.  Didn't change the Pivot Table, but using the argument T8&"" did the trick. 

       

      Thanks,

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Direct reference to a cell normally works, but the cell must contain something that is in the relevant part of the pivottable.
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor

    Depends on what's in T8. If that value isn't in the pivottable #REF! is the result. Please note that you might need to convert the content of T8 to text:
    =GETPIVOTDATA("Amount",'2019'!$R$4,"Fund","1100","Orgn","2002","Acct",T8&"")

    • rbarden's avatar
      rbarden
      Copper Contributor
      Hi Jan, I have a similar question except instead of returning a text value, I need to return a blank field from the pivot table as a number. The pivot table contains information about sales by each salesperson. Since some of the salespeople are new, there is blank (null?) data in some of the months and when I try to reference the sum of that particular month in my calculation, it returns an #REF! error. The formula is =GETPIVOTDATA("Total Ext. Price",$A$3,"SO Month",1). I've made sure the pivot table number format is a number.
    • Tom_at_work's avatar
      Tom_at_work
      Copper Contributor

      JKPieterse 

      Thanks Jan for your quick response.  Converting to text, based on your syntax worked, but please educated me on "how" this works.  I understand the concatenate function with the & symbol, but I don't understand how the syntax for this statement works.

       

      This worked!  Look forward to your response.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Adding an empty string to a number converts that number to a string value.

Resources