Getpivotdata with a lookup functions
 

Hi,

I have a worksheet called "summary" and cell A119 picks up data from another
sheet. Cell A119 contains a name from this other worksheet "Allotments".
Then there is another worksheet "holding" that contains a pivot table that
gains information from the "allotments" worksheet and refreshes automatically
when any changes are made to "Allotments". Therefore the range changes
depending on the data input into "Allotments".

I want to get data from the pivot table "holding" by looking up the name in
cell A119 on the "summary" worksheet. The pivot table has the names along
the top horizontal axis and the amount down the vertical axis.

What formula do i need in cell I119 on the "summary" worksheet to pick up
the grand total of the column with the name in cell A119 from the pivot table?

Sorry if this is confusing!!

Getpivotdata with a lookup functions
 

Found the answer myself with a little experimenting!

=GETPIVOTDATA("No. Shares Applied For",'holding'!$A$3,"Name of
Applicant",A119)

Cheers anyway

"Shaggyjh" wrote:

Hi,

I have a worksheet called "summary" and cell A119 picks up data from another
sheet. Cell A119 contains a name from this other worksheet "Allotments".
Then there is another worksheet "holding" that contains a pivot table that
gains information from the "allotments" worksheet and refreshes automatically
when any changes are made to "Allotments". Therefore the range changes
depending on the data input into "Allotments".

I want to get data from the pivot table "holding" by looking up the name in
cell A119 on the "summary" worksheet. The pivot table has the names along
the top horizontal axis and the amount down the vertical axis.

What formula do i need in cell I119 on the "summary" worksheet to pick up
the grand total of the column with the name in cell A119 from the pivot table?

Sorry if this is confusing!!