GetPivotData with column grand totals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I have a pivot table with the following layout:
[Count of results]
[Status] [Result]
P F Grand Total
A 10 3 13
B 15 8 23
C 5 4 9
Grand Total 30 15 45

A1 = [Count of results]

i am using the GetPivotData function to retrieve the grand totals but i can
only
retrieve the row grand totals (13, 23, 9) and not the column grand totals
(30, 15, 45).
Here is what i tried:
=GETPIVOTDATA(A1, "A") --> 13
=GETPIVOTDATA(A1, "Result") --> 45
=GETPIVOTDATA(A1, "P") --> #N/A
=GETPIVOTDATA(A1, "F") --> #N/A

I have tried using cell references but it doesn't make any difference.
I read many different how-tos about this function but the only solution i
found assumed the presence of column sub-totals.

What am i doing wrong? Help is appreciated
 
I think what you want is this:
=GETPIVOTDATA(A1,"Grand Total F")
=GETPIVOTDATA(A1,"Grand Total P")
 
Try this. GetPivot Data can refenece more than one dimension iun the second
argument...

=GETPIVOTDATA(A1, "Result F")
 
I tried both:
=GETPIVOTDATA(A1, "Result P")
=GETPIVOTDATA(A1, "Grand Total P")

but I still get #N/A. I noticed though that the above works when there is
only 1 value in the result column i specify in the formula (either P or F).

It seems to me that this formula's behaviour is not really stable, at least
not in Excel 2000 which I am using right now. I read about other users
reporting problems with it.

The reason I started to use it in the first place was to retrieve the totals
from the pivot table into another worksheet. First I thought about simply
doing it in VBA but then I was suggested to use this formula which looked
like a much better solution to me. Anyway I sure wasn't expecting it to be so
complicated...

Do you have any suggestions?

--
Thanks in advance
/Enrico


Jim Thomlinson said:
Try this. GetPivot Data can refenece more than one dimension iun the second
argument...

=GETPIVOTDATA(A1, "Result F")
--
HTH...

Jim Thomlinson


Enrico Campidoglio said:
Hi!

I have a pivot table with the following layout:
[Count of results]
[Status] [Result]
P F Grand Total
A 10 3 13
B 15 8 23
C 5 4 9
Grand Total 30 15 45

A1 = [Count of results]

i am using the GetPivotData function to retrieve the grand totals but i can
only
retrieve the row grand totals (13, 23, 9) and not the column grand totals
(30, 15, 45).
Here is what i tried:
=GETPIVOTDATA(A1, "A") --> 13
=GETPIVOTDATA(A1, "Result") --> 45
=GETPIVOTDATA(A1, "P") --> #N/A
=GETPIVOTDATA(A1, "F") --> #N/A

I have tried using cell references but it doesn't make any difference.
I read many different how-tos about this function but the only solution i
found assumed the presence of column sub-totals.

What am i doing wrong? Help is appreciated
Click to expand...
Click to expand...
 
I am using Excel 2000 and the formula I gave works for me regardless of how
many values are in the column. The results are dependent on your table setup
so I would need to know the entire layout. For the values included in the
table, did you choose Sum, Count, Max, Min...? Are the column grand totals
displayed? What is the text showing in the very upper left cell of the table
(e.g. "Sum of Value")? The key to using the formula GETPIVOTDATA is to
exactly match the column headings and row labels from your table - and the
results depend on the function used to summarize your data (for example, are
you showing the Sum of your numbers, or a count, or the min, max, ...?).

--
- K Dales


Enrico Campidoglio said:
I tried both:
=GETPIVOTDATA(A1, "Result P")
=GETPIVOTDATA(A1, "Grand Total P")

but I still get #N/A. I noticed though that the above works when there is
only 1 value in the result column i specify in the formula (either P or F).

It seems to me that this formula's behaviour is not really stable, at least
not in Excel 2000 which I am using right now. I read about other users
reporting problems with it.

The reason I started to use it in the first place was to retrieve the totals
from the pivot table into another worksheet. First I thought about simply
doing it in VBA but then I was suggested to use this formula which looked
like a much better solution to me. Anyway I sure wasn't expecting it to be so
complicated...

Do you have any suggestions?

--
Thanks in advance
/Enrico


Jim Thomlinson said:
Try this. GetPivot Data can refenece more than one dimension iun the second
argument...

=GETPIVOTDATA(A1, "Result F")
--
HTH...

Jim Thomlinson


Enrico Campidoglio said:
Hi!

I have a pivot table with the following layout:
[Count of results]
[Status] [Result]
P F Grand Total
A 10 3 13
B 15 8 23
C 5 4 9
Grand Total 30 15 45

A1 = [Count of results]

i am using the GetPivotData function to retrieve the grand totals but i can
only
retrieve the row grand totals (13, 23, 9) and not the column grand totals
(30, 15, 45).
Here is what i tried:
=GETPIVOTDATA(A1, "A") --> 13
=GETPIVOTDATA(A1, "Result") --> 45
=GETPIVOTDATA(A1, "P") --> #N/A
=GETPIVOTDATA(A1, "F") --> #N/A

I have tried using cell references but it doesn't make any difference.
I read many different how-tos about this function but the only solution i
found assumed the presence of column sub-totals.

What am i doing wrong? Help is appreciated
Click to expand...
Click to expand...
Click to expand...
 
The data field is calculated using the COUNT function and both column and row
grand totals are displayed. I also grouped some related rows together and
enabled drill-down.

After reading your suggestion I experimented with different column headings
and I found out that the formula works perfectly with some pivot item values
while I get #N/A with others. For example:

=GETPIVOTDATA(A1, "Result P") = Always #N/A
=GETPIVOTDATA(A1, "Result Passed") = Works
=GETPIVOTDATA(A1, "Result C") = Works
=GETPIVOTDATA(A1, "Result I") = Works
=GETPIVOTDATA(A1, "Result N") = Sometimes works, sometimes #N/A, apparently
depending on the headings of the other columns.

It seems that the formula has troubles handling 1-char item values or maybe
only some particular values. What do you think?
Anyway my solution is to rename the pivot items in the column to a word
instead of a letter and everything should work fine. I will report here if I
encounter any problems.

Thanks a lot for your help

--
/Enrico


K Dales said:
I am using Excel 2000 and the formula I gave works for me regardless of how
many values are in the column. The results are dependent on your table setup
so I would need to know the entire layout. For the values included in the
table, did you choose Sum, Count, Max, Min...? Are the column grand totals
displayed? What is the text showing in the very upper left cell of the table
(e.g. "Sum of Value")? The key to using the formula GETPIVOTDATA is to
exactly match the column headings and row labels from your table - and the
results depend on the function used to summarize your data (for example, are
you showing the Sum of your numbers, or a count, or the min, max, ...?).

--
- K Dales


Enrico Campidoglio said:
I tried both:
=GETPIVOTDATA(A1, "Result P")
=GETPIVOTDATA(A1, "Grand Total P")

but I still get #N/A. I noticed though that the above works when there is
only 1 value in the result column i specify in the formula (either P or F).

It seems to me that this formula's behaviour is not really stable, at least
not in Excel 2000 which I am using right now. I read about other users
reporting problems with it.

The reason I started to use it in the first place was to retrieve the totals
from the pivot table into another worksheet. First I thought about simply
doing it in VBA but then I was suggested to use this formula which looked
like a much better solution to me. Anyway I sure wasn't expecting it to be so
complicated...

Do you have any suggestions?

--
Thanks in advance
/Enrico


Jim Thomlinson said:
Try this. GetPivot Data can refenece more than one dimension iun the second
argument...

=GETPIVOTDATA(A1, "Result F")
--
HTH...

Jim Thomlinson


:

Hi!

I have a pivot table with the following layout:
[Count of results]
[Status] [Result]
P F Grand Total
A 10 3 13
B 15 8 23
C 5 4 9
Grand Total 30 15 45

A1 = [Count of results]

i am using the GetPivotData function to retrieve the grand totals but i can
only
retrieve the row grand totals (13, 23, 9) and not the column grand totals
(30, 15, 45).
Here is what i tried:
=GETPIVOTDATA(A1, "A") --> 13
=GETPIVOTDATA(A1, "Result") --> 45
=GETPIVOTDATA(A1, "P") --> #N/A
=GETPIVOTDATA(A1, "F") --> #N/A

I have tried using cell references but it doesn't make any difference.
I read many different how-tos about this function but the only solution i
found assumed the presence of column sub-totals.

What am i doing wrong? Help is appreciated
Click to expand...
Click to expand...
Click to expand...
 
Back
Top