Solved

Problems with GETPIVOTDATA

Posted on 2014-10-13
13
98 Views
Last Modified: 2014-10-13
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
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40377371
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
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40377451
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
 

Author Comment

by:Frank Freese
ID: 40377463
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40377466
please check the last file i uplloaded
0
 

Author Comment

by:Frank Freese
ID: 40377489
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
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40377509
Let me check
0
 

Author Comment

by:Frank Freese
ID: 40377527
thank you kindly
0
 

Author Comment

by:Frank Freese
ID: 40377554
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
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40377674
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
 

Author Comment

by:Frank Freese
ID: 40377763
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
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40377908
yes. it is fixed. please find attached.
Book2--1-.xlsx
0
 

Author Closing Comment

by:Frank Freese
ID: 40377946
Fantastic and thank you very much.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40377967
you are welcome.   glad to help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
converting excel into labels 6 37
Excel formula to calculate ID # 4 43
Excel VBA Script 9 57
Inserting records in Access database using Excel VBA 4 13
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question