Oct 28 2019 07:04 AM
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
Oct 28 2019 08:32 AM - edited Oct 28 2019 08:32 AM
SolutionDepends 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&"")
Oct 28 2019 08:35 AM
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.
Oct 28 2019 08:41 AM
Oct 28 2019 09:01 AM
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.
Oct 28 2019 09:04 AM
Thanks Patrick. Didn't change the Pivot Table, but using the argument T8&"" did the trick.
Thanks,
Oct 28 2019 09:04 AM
May 09 2022 10:59 AM
Oct 28 2019 08:32 AM - edited Oct 28 2019 08:32 AM
SolutionDepends 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&"")