Dear Sir,

I have contructed a Pivot Table below:


A B C D E F G H
Document: CN CN Total DN DN
Total G. Total
Source : AR GL AR GL
Jan-06 110 870 980 600 350 950 1930
Feb-06 160 600 760 800 250 1050 1810
Mar-06 180 800 980 900 360 1260 2240
Apr-06 220 900 1120 200 280 480 1600
May-06 350 200 550 210 220 430 980
Jun-06 540 100 640 360 650 1010 1650
Jul-06 800 950 1750 420 220 640 2390
Aug-06 700 780 1480 740 350 1090 2570
Sep-06 500 740 1240 620 450 1070 2310
Oct-06 900 250 1150 520 330 850 2000
Nov-06 400 360 760 410 140 550 1310
Dec-06 390 840 1230 940 870 1810 3040
G. Total 5250 7390 12640 6720 4470 11190 23830

At default, column D is = B+C, column G is = E+ F, and column H is = D+G.

May I know is there is a way to change this default to D = C- B, G=F-E and
H=B+C ?

I can easily add these formulas at column I, J and K, however for
presentation purpose i still need the answer to be at column D, G and H.

In pivot table, column can be hide but not inserted, am I right ?

Please advise me.


Thank you

Low Seng Kuang




--
A36B58K641

In the source data, you could add a column to calculate a negative
amount if the type is AR, and positive if GL.

Use that field in the pivot table, instead of the Amount field.

Mr. Low wrote:
Dear Sir,

I have contructed a Pivot Table below:


A B C D E F G H
Document: CN CN Total DN DN
Total G. Total
Source : AR GL AR GL
Jan-06 110 870 980 600 350 950 1930
Feb-06 160 600 760 800 250 1050 1810
Mar-06 180 800 980 900 360 1260 2240
Apr-06 220 900 1120 200 280 480 1600
May-06 350 200 550 210 220 430 980
Jun-06 540 100 640 360 650 1010 1650
Jul-06 800 950 1750 420 220 640 2390
Aug-06 700 780 1480 740 350 1090 2570
Sep-06 500 740 1240 620 450 1070 2310
Oct-06 900 250 1150 520 330 850 2000
Nov-06 400 360 760 410 140 550 1310
Dec-06 390 840 1230 940 870 1810 3040
G. Total 5250 7390 12640 6720 4470 11190 23830

At default, column D is = B+C, column G is = E+ F, and column H is = D+G.

May I know is there is a way to change this default to D = C- B, G=F-E and
H=B+C ?

I can easily add these formulas at column I, J and K, however for
presentation purpose i still need the answer to be at column D, G and H.

In pivot table, column can be hide but not inserted, am I right ?

Please advise me.


Thank you

Low Seng Kuang






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

Dear Debra,

Yes, your suggestion is helpful.

Many thanks

Low


--
A36B58K641


"Debra Dalgleish" wrote:

In the source data, you could add a column to calculate a negative
amount if the type is AR, and positive if GL.

Use that field in the pivot table, instead of the Amount field.

Mr. Low wrote:
Dear Sir,

I have contructed a Pivot Table below:


A B C D E F G H
Document: CN CN Total DN DN
Total G. Total
Source : AR GL AR GL
Jan-06 110 870 980 600 350 950 1930
Feb-06 160 600 760 800 250 1050 1810
Mar-06 180 800 980 900 360 1260 2240
Apr-06 220 900 1120 200 280 480 1600
May-06 350 200 550 210 220 430 980
Jun-06 540 100 640 360 650 1010 1650
Jul-06 800 950 1750 420 220 640 2390
Aug-06 700 780 1480 740 350 1090 2570
Sep-06 500 740 1240 620 450 1070 2310
Oct-06 900 250 1150 520 330 850 2000
Nov-06 400 360 760 410 140 550 1310
Dec-06 390 840 1230 940 870 1810 3040
G. Total 5250 7390 12640 6720 4470 11190 23830

At default, column D is = B+C, column G is = E+ F, and column H is = D+G.

May I know is there is a way to change this default to D = C- B, G=F-E and
H=B+C ?

I can easily add these formulas at column I, J and K, however for
presentation purpose i still need the answer to be at column D, G and H.

In pivot table, column can be hide but not inserted, am I right ?

Please advise me.


Thank you

Low Seng Kuang






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

You're welcome. Thanks for letting me know that it helped.

Mr. Low wrote:
Dear Debra,

Yes, your suggestion is helpful.

Many thanks

Low


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