• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Query in Getpivotdata function in pivot table

S

Sakthikumar Chinnasamy

Member
I want to whether we can use getpivotdata function for filters area in pivot table.

For eg. Pivot table contains rows as product list, values as "Sum of Sales" and Filters as Week number(1,2,3). In next sheet, B1 cell contain - Product name and B2 cell contain week number # 3.

Output require - In B3 cell, i want sum of sales values based on b1 & b2 cell reference using getpivotdata function.
 
Please upload a sample file.
When it comes down to GETPIVOTDATA, just start in the result cell then type = and point (with thee mouse) to the required field of the pivot.
You now have a hard coded link. You will see in the argument of the formula what values are used referring to the pivot row/col coordinates. These can be replaced by cell references containing those values. Now you can drag down and across. Wrap around iferror in case the pivot can get filtered or in case sometimes these values may not appear in the pivot.
 
Hi, the week-opened must be either a col or row label of the pivot and not a filter object. I put those in the columns and got rid of "priority".
You could use slicers to "filter" the pivot.
formula =GETPIVOTDATA("Priority",Pivot!$A$10,"Service Line",A2,"Opened - Week",B2)
 
You must log in or register to reply here.
Back
Top