Forum Discussion
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
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.
Thanks Patrick. Didn't change the Pivot Table, but using the argument T8&"" did the trick.
Thanks,
- Direct reference to a cell normally works, but the cell must contain something that is in the relevant part of the pivottable.
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&"")- 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.
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.
- Adding an empty string to a number converts that number to a string value.
