Can I pull field as datafield or as a output from a pivot table re

[Vikram Dhemare]

Hi Friends,

I have Pivot table something like below mentioned :
Data
CHILD PART NO. FG PART NO. Sum of TOTAL
1010051 ATM0609 11556
1010160 ATM0415 144
PTM0606 2792
PTM0607 3800
1010161 ATM0412 6551
1010162 ATM0415 144
PTM0606 2792
PTM0607 3800

Now, I wish to pull the data "FG PART NO." (not the sum of total) as a
result. Is this possible by using "getpivotdata" function.

Hope u understood my question.
Thanks in advance.

Vikram P. Dhemare

 

[Debra Dalgleish]

The GetPivotData function can return the data and totals from the pivot
table, but not the field item text.

You can link to a cell that contains a part number, e.g. =B8

 

[Vikram Dhemare]

Thanks Mr. Debra Dalgleish

Is there any option to return the text value? could you give formula with
example

 

[Debra Dalgleish]

If you provide details on what value you want to return, someone may be
able to help with a formula.

 

[Vikram Dhemare]

Here is the pivot table :
A B C
RC1 1010051 ATM0609 11556
RC2 1010160 ATM0415 144
RC3 PTM0606 2792
RC4 PTM0607 3800
RC5 1010161 ATM0412 6551
RC6 1010162 ATM0415 144
RC7 PTM0606 2792
RC8 PTM0607 3800
The result would be:

1010162 =INDEX(A1:C8,MATCH(A10,A1:A8,3),row(2),col.(2)) i.e. the returned
field value would be PTM0606
=INDEX(A1:C8,MATCH(A10,A1:A8,3),row(3),col.(2)) i.e. the returned field
value would be PTM0607
I have tried this:
in Cell B10 =INDEX(A1:C8,MATCH(A10,A1:A8,2),2)
in Cell B11 =INDEX(A1:C8,MATCH(A10,A1:A8,3),2)
but the value is returning ATM0415 i.e the first corresponding row value of
given criteria.
Pl. help me out as I am in desparately need the solution for this.

 

[Debra Dalgleish]

The following formula would return PTM0606 with 1010162 in cell A10:

=OFFSET(A1,MATCH(A10,A1:A8,1),1)

 

[John James]

Hi Debra,

This is interesting. Pivottables are so quick and easy to create, and
so useful, but I've not been able to effectively re-use the data
summarised by pivottables into other reports without retyping.

I assume that if the source data changed (say another part number
appear in the following month, then formulae extracting figures from
the pivottable may become invalid.

I understand that any figure in pivottables can be drilled down on to
give all source entries - which is fantastic - but this is the only
report I've been able to effectively reprocess pivottable data into.

Is there any source which explains how to re-use and reprocess
pivottable data? Any tips & tricks?

Thanks,

 

[Debra Dalgleish]

The sample formula in my previous post will pull a field item from the
pivot table. To pull data, you can use the GetPivotData function, as
described in Excel's Help, and here:

http://www.contextures.com/xlPivot06.html

 

[John James]

Thanks Debra. Much clearer now.