Pivot with totals on top

[houghi]

Hello,

I run excel 2000 (company policy) and I have the following in a Pivot
table:

User Day Duration
user1 30
Monday 10
Tuesday 20
user2 12
Monday 5
Tuesday 7
Total 42

So the subtotals already are on top. However I would like to have it
like:
User Day Duration
Total 42
user1 30
Monday 10
Tuesday 20
user2 12
Monday 5
Tuesday 7

The reason is then the totals are always in the same place, making it
easier to link to those numbers.

I could use 2 pivot tables, one with the totals and one with the
subtotals. However is it possible to do it with one?


houghi
--

 

[Roger Govier]

Hi
Grand Total by Row is always at the far right of the PT
Grand Total by Column is always at the bottom on the PT.
There is no way of changing that.

Regardless of the position, if you use the GetPivotData function, rather
than a cell reference, it will always pick up your totals for you.
For help on the use of GetPivotdata take a look at

http://www.contextures.com/xlPivot06.html

 

[houghi]

Hi
Grand Total by Row is always at the far right of the PT
Grand Total by Column is always at the bottom on the PT.
There is no way of changing that.

Bummer. With other pivot tables, I was able to work around it by adding
everything in the colomn and not showing the total. However now I need
the subtotals as well.

Anyway, it stil is good to know that it is not possible. That way I do
not need to look further. Thanks for that.

Regardless of the position, if you use the GetPivotData function, rather
than a cell reference, it will always pick up your totals for you.
For help on the use of GetPivotdata take a look at

http://www.contextures.com/xlPivot06.html

The page starts with "In Excel 2002, and later versions" and I am
running 2000 and no way I will be able to change that. :-(

Thanks for the site as well.

houghi
--

 

[Roger Govier]

Hi

You still can
Double click on the field you want to Subtotal on>Subtotals>automatic
Right click on PT>Table options>Grand Total by Column>de-select
Outside of your PT (above the PT if you want) =SUM(C5:C1000)/2
where C5:C100 is a large enough range to cove the column you want totaled.

 

[Debra Dalgleish]

In Excel 2000 you can still use the GetPivotData formula, but it's not
created automatically when you link to a pivot table data cell.

You can create the GetPivotData formula manually, by typing an equal
sign, the function name, and the required arguments, just as you would
for any other function. See Excel Help for examples and instructions.

 

[houghi]

Hi

You still can
Double click on the field you want to Subtotal on>Subtotals>automatic
Right click on PT>Table options>Grand Total by Column>de-select
Outside of your PT (above the PT if you want) =SUM(C5:C1000)/2
where C5:C100 is a large enough range to cove the column you want totaled.

Yes, although it will be a pain to recalculate percentages and such. I
guess a new one pivot will be easier. I am sure at at one point I will
go over the limit. Sure I could select everything till the last row.

houghi

 

[houghi]

In Excel 2000 you can still use the GetPivotData formula, but it's not
created automatically when you link to a pivot table data cell.

You can create the GetPivotData formula manually, by typing an equal
sign, the function name, and the required arguments, just as you would
for any other function. See Excel Help for examples and instructions.

When I select one sum as in
http://www.bettersolutions.com/excel/EUA153/YI030910881.htm it works
somewhat. However when I select more then one, it gives me errors all
over the place.

Also I am unable to select "Sales" as provided in the sample above, even
though I have typed the content in exactly the identical fields. It
works when I use B13.

So from what I see, it is -for me_ a non working solution.

I will just have a second pivot table with just the totals.

houghi

 

[Roger Govier]

Hi

With the example you mention, you would need to amend as follows
=GETPIVOTDATA("Sales",B13,"Month","Apr","Name","Richard") = 900

=GETPIVOTDATA("Sales",B13,"Month",C$14,"Name",$B15)
Copy down and across as required

 

[houghi]

Hi

With the example you mention, you would need to amend as follows
=GETPIVOTDATA("Sales",B13,"Month","Apr","Name","Richard") = 900

=GETPIVOTDATA("Sales",B13,"Month",C$14,"Name",$B15)
Copy down and across as required

I am doing something utterly wrong, I think. No matter what I do I keep
getting errors that the formula is wrong. I re-read everything 10 times
and used copy and paste and still nothing. :-(

I am getting pretty frustrated after all this time.

houghi

 

[Debra Dalgleish]

The example link you posted is not for Excel 2000 -- it's for Excel 2002
or later versions.
In Excel 2000, use the item names within one set of quote marks. For
example, if the pivot table starts in cell B13:

=GETPIVOTDATA($B$13,"Apr Richard")

 

[houghi]

The example link you posted is not for Excel 2000 -- it's for Excel 2002
or later versions.
In Excel 2000, use the item names within one set of quote marks. For
example, if the pivot table starts in cell B13:

=GETPIVOTDATA($B$13,"Apr Richard")

OK. This works with one minor change. I need to replcae the `,` with a
`;` Otherwise perfect. May thanks (and now on to management to ask them
to please update Excel)

Now I can go and re-write all the excel files, making it a LOT easier
for me to get my weekly statistics out. I already went down from some 16
hours to 4. Now I should be ready in 2. Mmm. Should I tell that to
management or not. :-D

Thanks again. This will also (partly) solve the other issues I had with
Pivot tables.

houghi