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
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

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
--
Regards
Roger Govier

"houghi" wrote in message
...
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
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Roger Govier wrote:
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
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

Hi

You still can
Double click on the field you want to Subtotal onSubtotalsautomatic
Right click on PTTable optionsGrand Total by Columnde-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.
--
Regards
Roger Govier

"houghi" wrote in message
...
Roger Govier wrote:
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
--
Knock-knock.
Who's there?
Under the Patriot Act, we don't have to tell you that.

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 wrote:
Roger Govier wrote:

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Roger Govier wrote:
Hi

You still can
Double click on the field you want to Subtotal onSubtotalsautomatic
Right click on PTTable optionsGrand Total by Columnde-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
--
I do not want life insurance.
I want all people to be genuinely grieving when I die.

houghi

Debra Dalgleish wrote:
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...I030910881.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
--
I do not want life insurance.
I want all people to be genuinely grieving when I die.

houghi

Hi

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

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

--
Regards
Roger Govier

"houghi" wrote in message
...
Debra Dalgleish wrote:
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...I030910881.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
--
I do not want life insurance.
I want all people to be genuinely grieving when I die.

houghi

Roger Govier wrote:
Hi

With the example you mention, you would need to amend as follows
=GETPIVOTDATA("Sales",B13,"Month","Apr","Name","Ri chard") = 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
--
Always listen to experts. They'll tell you what can't be done,
and why. Then do it.
-- Heinlein : Time Enough For Love

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 wrote:
Roger Govier wrote:

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",$B 15)
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
Contextures
http://www.contextures.com/tiptech.html

Debra Dalgleish wrote:
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
--
Remind me to write an article on the compulsive reading of news. The
theme will be that most neuroses can be traced to the unhealthy habit
of wallowing in the troubles of five billion strangers. -- Heinlein