Hello,
You’re on the right track to putting automatically updated grand totals on
your pivot charts, but there are a couple of problems you might encounter
with that technique.
Here’s one solution that admittedly lacks elegance, but works. It’s what
you would expect to be able to do in one step, but Excel won’t permit that.
It works just the way you want if you do it in two steps, strangely.
Step 1: Create another worksheet in your workbook as a staging area for
the totals you want to collect. Eg.) For human resource work force
analysis:
| A | B
--+---------------------+--------------------------------------------------------
1 | Total # of Poets | For each row in this column, enter the equals
sign, and
| | point at the wily grand total you want to capture
on the
| | PivotTable worksheet. The result will be something
like:
| | =GETPIVOTDATA(….)
--+---------------------+--------------------------------------------------------
2 | Total # of Skiers | =GETPIVOTDATA(….)
--+---------------------+--------------------------------------------------------
3 | Total # Employees | =GETPIVOTDATA(….)
--+---------------------+--------------------------------------------------------
Step 2: As per Jon’s instructions, go to the Pivot Chart and click anywhere
to select the chart. Create a calculated text field by entering = in the
formula bar, and point at the column B value in the intermediate worksheet.
These totals will change automatically and correctly as you change the values
of the dynamic fields on your pivot chart and pivot table.
Why does this work when we’re not permitted to enter the GETPIVOTDATA
function directly in the pivot chart formula bar? Why is the sky blue?
This is also the way to get around other restrictions on the functions you
can use in that context, for example, putting the current date on a report
with the TODAY() function.
Regards,
Margaret
"Jon Peltier" wrote:
> Oh yeah, I forgot about that PITA. You have to type in the cell reference if the
> cell resides within a pivot table, because a formula in a textbox or other chart
> element must refer only to a range address or a defined name. You need the sheet
> name also, so after pressing =, type something like this:
>
> =Sheet1!A1
>
> if the sheet name has spaces, you need to surround it with single quotes:
>
> ='Sheet One'!A1
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Colleen T wrote:
>
> > When I try this (link to the pivot table's summary total) I get an error
> > saying not a valid function. The function line contains
> > =GETPIVOTDATA("Personnel no.",'P2'!$A$3)
> >
> > "Jon Peltier" wrote:
> >
> >
> >>Colleen -
> >>
> >>You can add textboxes to the chart, which are linked to worksheet cells. Select the
> >>chart, press the equals key, then navigate to and click on the cell and press Enter.
> >> The contents of the cell are displayed in a new textbox in the middle of the
> >>chart. Move and format this textbox, and add the next one.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>Colleen T wrote:
> >>
> >>
> >>>I created a pivot table which shows the both individual and grand totals
> >>>based on the fields I selected. I then created a bar chart based on this
> >>>pivot table. What I would like to do is show the grand totals as well as the
> >>>individual totals. Is this possible, if so how do I do it?
> >>
> >>
>
>
Similar Threads
