Problems with GETPIVOTDATA

Folks,
In trying to understand the GETPIVOTDATA function and I not able to get the results I am seeking.
In the attached workbook I have a data table and a pivot table.
In cells G3:K3 are header labels.
G4:G5 are labels. In G6 there's a label and the "Year" needs to be come from H4 and I'm having a problem there also.
In H4 and H5 I'm using a Data Validation List for the Year and the Country.
In cell I6 and J6 there are GETPIVOTDATA functions where I'm trying to pull the Year from  H4 and the Country from H5 then get a total.
In other words if I select a Year from H4 and a Country from H5 then only the data for that Year for that Country would appear. If I select Year(s) and all Country(s) then I'd get all the data.
Hopefully, I've made myself clear.
Thanks
Book2.xlsx
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
Look,

i do not understand why someone would wrap the getpivotdata in If and ifferror and the way logical test is put here makes no sense at all.

but coming back to your question.

the syntact for get pivot data here is

GETPIVOTDATA("Order Amount",$G$21,"Country","UK")

first Order amount is Data Field to return, then G21 is the reference to the pivot table, then Country is the first Field, and then following the "UK"  is the item on the Country Field.

the GetPivotData can have one or too many Field and Items,


if you would want to test this by your self.  put an equal sign in a cell and then click an amount of the pivot table and you would see that getpivotdata automatically puts all these syntax references by itself.
0
ProfessorJimJamCommented:
see attached file which i have fixed it for you.

plz let me know if that is what you were looking for.
Book2--1-.xlsx
0
Frank FreeseAuthor Commented:
The reason for error checking was without it I was getting an #REF! error when selecting UK at times.
As I understand what you're saying is GETDATAPIVOT cannot work on a dynamic cell, such as Year or Country?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

ProfessorJimJamCommented:
please check the last file i uplloaded
0
Frank FreeseAuthor Commented:
When I select from your workbook the year 2010 in H4 and the country UK in H5, I get #REF! errors in J6 and K6. Do you get the same?
0
ProfessorJimJamCommented:
Let me check
0
Frank FreeseAuthor Commented:
thank you kindly
0
Frank FreeseAuthor Commented:
I did find this out. In my Pivot Table I have to show all years and countries to avoid the #REF! error. See if that works for you.
0
ProfessorJimJamCommented:
yes.   when you are referring to a pivot table then pivot table data shall be available. otherwise it will give error. so you found it yourself that all years have to be available in pivot data.

i have also came up with an alternative way that you do not need to have a pivot table to implement this calculation.

see attached file . better than getpivotdata  the sumproduct function that works better i guess.
Book2--1-.xlsx
0
Frank FreeseAuthor Commented:
I'm not very impressed with the GETPIVOTDATA function - it seem more of an after thought from Microsoft.
Before awarding you well earned points there's one hic-up - when you select a year and company it returns the total for both companies not just the one company. Can you fix that, if possible?
Thank you
0
ProfessorJimJamCommented:
yes. it is fixed. please find attached.
Book2--1-.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Fantastic and thank you very much.
0
ProfessorJimJamCommented:
you are welcome.   glad to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.