Grand total in seperate cell for Pivot table

[christhedonstar]

Hi,

How do I get the "grand total" from a pivot table into cell B2 (for example).

When I do = to the cell Excel automatically creates the formula

=GETPIVOTDATA("Stuff",$Z$12)

But when I change the pivot table then I lost the total. I want to get the total of the data the pivot table uses. The reason is so that the user can check the results of the pivot table against some of the raw data. He doesn't trust the pivot table.

Thanks,

Chris

 

[SkipVought]

Hi,

I would never reference a specific cell to get a TOTAL from a PivotTable.

Rather reference the COLUMN that the totals are in, using the MAX or MIN function, as appropriate.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

Thanks for the reply,

Sorry I don't understand, the aim is to trust the pivot table result and check it is using the correct data. I guess the first question is it possible without writing code? If so what is the formulae? If its easy I guess I'll put it in to appease people.

Thanks,

Chris

 

[SkipVought]

Please post your PT, showing the total(s) & cell ref(s) of inteest.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

Chris 1
John 2
Grand Total 3

Chris is in Cell A1

So I want cell B3, but sometimes I change the table so its displaying totally different information, but I still want the grand total from the pivot table. Is there a formula to do this?

Thanks,

Chris

 

[SkipVought]

[tt]
=MAX(B:B)
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

The specific cell referenced in the OP is just the top left most cell of the Pivot Table. The location defaults to cell A3 in a new tab. So the formula
[tab]=GETPIVOTDATA("Stuff",$Z$12)
would return the Total of of Stuff. If the Pivot Table is set up to display the SumOfStuff, then you'd get the Grand Total of the Stuff column. (all of this assumes that your Pivot Table starts on cell Z12)



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I like John's approch.

I use the MAX function when displaying TOTALS above the PivotTable, especially with COLUMN fields, so users don't have to scroll down to see the "bottom line". In this case, I don't refererence the entire column, but the top row in the PT to the last row in the sheet (65,536)

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

A potential draw back to MAX is that the grand total may not be the largest number in the column.

[tt]
1
5
15
-10
Total: 11

Max: 15[/tt]


GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[SkipVought]

Yup.

In the cases I face on a regular basis, I am dealing with Pieces and Machine loads, which are all positive values. One must be careful when applying a specific to a given case.

The other approch I have used, especially when there might be multiple Row Totals, is to use a SUMPRODUCT, using the ROW value or ROW Total headings as criteria.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

Yeah max wouldn't work in this scenario either, the numbers in the pivot are negative and positive.

Also =GETPIVOTDATA("Stuff",$Z$12) changes when I click a different button on the pivot table as anotherhiggins suggested.

I would have thought you could query a pivot table for a specific thing with the function even if the pivot table isn't currently showing it?

 

[christhedonstar]

What about a UDF in VBA to do this? What are thoughts on that?

 

[xlbo]

If the pivottable will always be in teh same place (columns), why not use vlookup?

=vlookup("Grand Total",A:E,5,false)

Where pivot table starts in colA and total column is E

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

Why not trust GETPIVOTDATA function? According to the help file, it returns data stored in PT report and displayed in the report. For me it works ok, and return proper result after refreshing data or rearranging it, as long as I do not remove/rename related value in string argument. Moreover, it is flexible enough to return domain results.
I would rather use database to summarise data if I didn't trust excel pivot table.

combo

 

[christhedonstar]

I have some buttons which change the pivot table. So the grand total will be different for different results table.

combo I tried amending =GETPIVOTDATA("Stuff",$Z$12) but didn't know how to give it a reference to the pivot table other than the $Z$12, but that didn't work.

 

[AnotherHiggins]

So does the actual location of the pivot table change? Is it ever anywhere other than Z12?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[christhedonstar]

Ok that prompted an idea for me:

=IF(Z12="Sum of Stuff I want",GETPIVOTDATA(Z12,$Z$12), "I'll do something else")

 

[xlbo]

Surely the column which holds the text "Grand Total" doesn;t move? I guess you might have differing numbers of columns to display tho.....

If you really wanted a UDF then:

dim fCell as range

Set fCell = sheets("Sheetname").columns(1).find("Grand Total")

rw = fCEll.row
col = cells(fcell.row,256).end(xltoleft).column

TheAnswer = cells(rw,col)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[christhedonstar]

Ok: this is good enough:

=IF(Z12="Sum of stuff I want",IF(GETPIVOTDATA(Z12,$Z$12)=X172,"OK", "NOT OK"), "Not displaying default view")

Then when I hit the buttons, I do a calculate on the sheet to update it (For some reason it didn't update the calc tree when I hit the button to change the pivot and then hit f9 manually)

 

[SkipVought]

You may have Manual Calculation.

Tools > Options -- Calculation tab.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}