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

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

--
Regards

Roger Govier


"Dinesh" wrote in message
...
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

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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Dinesh" wrote in message
...
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

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.

--
Regards

Roger Govier


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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Dinesh" wrote in message
...
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

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" wrote:

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.

--
Regards

Roger Govier


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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Dinesh" wrote in message
...
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

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.

--
Regards

Roger Govier


"Dinesh" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"Dinesh" wrote in message
...
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

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.