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 wi...
excel
Formulas and Functions
office 365
  • JKPieterse's avatar
    JKPieterse
    Oct 28, 2019

    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&"")

Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 28, 2019

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
    Oct 28, 2019

    Patrick2788 

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

     

    Thanks,

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

Resources