| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetPivotData with column grand totals
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 -- Thanks in advance /Enrico |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
GetPivotData with column grand totals
I think what you want is this:
=GETPIVOTDATA(A1,"Grand Total F") =GETPIVOTDATA(A1,"Grand Total P") -- - K Dales "Enrico Campidoglio" wrote: 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 -- Thanks in advance /Enrico |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
GetPivotData with column grand totals
Try this. GetPivot Data can refenece more than one dimension iun the second
argument... =GETPIVOTDATA(A1, "Result F") -- HTH... Jim Thomlinson "Enrico Campidoglio" wrote: 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 -- Thanks in advance /Enrico |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
GetPivotData with column grand totals
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" wrote: Try this. GetPivot Data can refenece more than one dimension iun the second argument... =GETPIVOTDATA(A1, "Result F") -- HTH... Jim Thomlinson "Enrico Campidoglio" wrote: 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 -- Thanks in advance /Enrico |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
GetPivotData with column grand totals
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" wrote: 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" wrote: Try this. GetPivot Data can refenece more than one dimension iun the second argument... =GETPIVOTDATA(A1, "Result F") -- HTH... Jim Thomlinson "Enrico Campidoglio" wrote: 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 -- Thanks in advance /Enrico |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
GetPivotData with column grand totals
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" wrote: 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" wrote: 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" wrote: Try this. GetPivot Data can refenece more than one dimension iun the second argument... =GETPIVOTDATA(A1, "Result F") -- HTH... Jim Thomlinson "Enrico Campidoglio" wrote: 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 -- Thanks in advance /Enrico |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a pivot tables Grand Totals Column | Excel Discussion (Misc queries) | |||
| Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
| Grand Totals with Nested Sub Totals | Excel Discussion (Misc queries) | |||
| Excel - how do I show column grand totals on the pivot chart | Charts and Charting in Excel | |||
| How do I fix my Stacked column chart with grand totals? | Charts and Charting in Excel | |||
![Enrico Campidoglio[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
