Is there any way of combining vlookup with getpivotdata?

[Sarah (OGI)]

Is there any way of combining a vlookup with the getpivotdata function?

I have two worksheets. The first contains a pivot table. The data looks
similar to example below:

A B C D E
1 Client Ref Client Name Class Product Total
2 ABC Company ABC A Home 1
3 Travel 2
4 A TOTAL 3
5 ABC TOTAL 3
6 XYZ Company XYZ A Home 10
7 A TOTAL 10
8 B Car 20
9 B TOTAL 20
10 XYZ TOTAL 30

In the second worksheet, I want to show an overview of the details, so
listed in column A, I have a list of each of the client references (those
shown in column A of pivot table). I want to vlookup against each of these
client references listed in worksheet 2, picking out the value corresponding
to "A Total" on worksheet 1.

So for client ref ABC listed on worksheet 2, I want to show a value of "3"
for "A Total". For client ref XYZ, I want to show "10" as "A Total" and "20"
as "B Total".

Any ideas?

 

[Bernie Deitrick]

Sarah,

Simply insert a copy of the pivot table and hide details to get to the table that you
want.....That's the beauty of Pivot tables.


Or

Use the =GETPIVOTDATA() function, and change the appropriate parameter to a cell reference.
For example:

=GETPIVOTDATA("Value",Sheet2!$A$3,"Client Ref","ABC","Client Name","Client
ABC","Class","A","Product","Home")

would become

=GETPIVOTDATA("Value",Sheet2!$A$3,"Client Ref",A2,"Client Name",B2,"Class","A","Product","Home")

where column A will have the client ref values, and column B will have the Client name.

HTH,
Bernie
MS Excel MVP