Extract value from Specific Cell

[Guest]

Pivot table gives a "Grand Total" of all rows at the end of row. Each time I
run a Pivot Table, the Grand Total Column is changing from column to column.
Some time the Grand Total can be find in col "O", "P", "Q" or "R" due to what
is being fetched. What type of formula can be build where it can look the
Grand total column and link it to the Grand totals of row by row?

For Example, if a Grand total is on col "P", Row #2, Col "P" to be link.

If a Grand total is on Col "Q", Row #2, col "Q" to be link.

Thanks in advance for your help.

Dinesh

 

[Roger Govier]

Hi Dinesh

Take a look at the use of the GetPivotData function. This will solve
your problem.
For help on this go to Debra Dalgleish's site
http://www.contextures.com/xlPivot06.html

 

[Guest]

Thanks Roger. Looked at it, I am not able to target the specific Fields in
the Pivot table.

 

[Roger Govier]

Hi Dinesh

I am not able to target the specific Fields in the Pivot table.

I don't understand why not.
You want the Grand Total Column, and you want the result where say
A4="yourvalue"
As you copy down, it will pick up the values where A5="your next Value"
etc.

 

[Guest]

Hi Roger,

I have tried =GetPivotData($a$4,"Grand Total") some how it doesn't work.
Isn't there a simple way to extract total of a rows if the header of the
column is "Grand Total" between col "O" to "R"?

Thanks,
Dinesh

 

[Roger Govier]

Hi Dinesh

The formula won't be as simple as that, and without knowing the full
layout of your PT I can't give the formula to use.
However, if you install the Generate GetPivotData feature turned on as
described by Debra, and then type = and point to first cell that you
wish to extract data from, it will generate the correct syntax of the
formula for you.

Once you have done this, you will be able to amend the references in the
generated formula to take variable references to suit your needs.

It will work if you persevere.

 

[Herbert Seidenberg]

Here is how you might get into trouble if you do not follow
Roger's advice:
....type "=" and point to first cell that you wish to extract data
from...
You might instead fill in the Function Arguments of GETPIVOTDATA().
Assume your PT is located at A1 and looks like this:

Sum of Value Column
Row 2/1 2/2 2/3 Grand Total
DE 6 2 1 9
EA 4 6 4 14
EP 6 7 7 20
FM 4 9 5 18
GK 7 2 2 11
JH 3 1 9 13
KM 4 9 6 19
MG 2 4 4 10

If you want to extract the 20 in the Grand Total, you might write:
=GETPIVOTDATA("Value",E3:E10,"Row","EP")
which will give you the correct result.
However, if you unselect Column 2/3, you get #REF!
If you follow Roger's method, you get this formula:
=GETPIVOTDATA("Value",A1,"Row","EP")
and the result will be the row Grand Total, independent of
the number of columns.