Sometimes when I post a question here I never see it, but one time when
Googling the subject I found my question posted in this newgroup, but when I
go through the Microsoft website, it's not visible there. I posted a
question this morning about where to find the option to insert a calculated
field, and that question has never appeared here. I did find the answer to
it, however. I have another question now, and hope this will show up.

I have this pivot table:
Requested Ship Date CLEAR STOCK GREEN STOCK CLEAR GREEN
STOCK 4025 177
Mar-10-09 150
Mar-12-09 600 75
Mar-13-09 75
Mar-16-09 375
Mar-20-09 450 75
Grand Total 4025 177 1650 150


I hope those figures will remain intact and won't be all over the place once
this is posted. I want to insert two fields that will calculat a decreasing
balance of the clear and the green. Results of those two fields based on the
pivot table above would be:

Clear Balance Green Balance
3875 177
3275 102
3200 102
2825 102
2375 27

Hope I haven't asked too much. Hope this will post visibly to me, and hope
the tables will post without running all over the screen. My apologies if
they do and I won't blame anyone for not answering. Thank you. Connie

Hi Connie

I think I would try to tackle it a slightly different way - if you have that
option.
I would have my source data as
Date Clear Green
Stock 4025 177
Mar 10 -150
Mar 12 -600 -75
Mar 13 -75
Mar 16 -375 -75
Mar 20 -450

Then in Pivot Table Layout
Drag Date to Row area
Drag Clear to Data area
Drag Clear to Data area again
Drag Green to Data area
Drag Green to Data area again

Ensure all are set to Sum
On Clear2 and Green 2OptionsShow data asRunning Total inBase fieldDate

Change the Titles form Sum of Clear and Sum of Clear2 to Clear Items and
Clear Balance or whatever terminology suits you.

If you need to convert your existing data to make the daily transactions
negative, just enter -1 in a spare cellCopy that cellSelect the range of
transactions in D and EPaste SpecialMultiply.
--
Regards
Roger Govier

"Connie Martin" wrote in message
...
Sometimes when I post a question here I never see it, but one time when
Googling the subject I found my question posted in this newgroup, but when
I
go through the Microsoft website, it's not visible there. I posted a
question this morning about where to find the option to insert a
calculated
field, and that question has never appeared here. I did find the answer
to
it, however. I have another question now, and hope this will show up.

I have this pivot table:
Requested Ship Date CLEAR STOCK GREEN STOCK CLEAR GREEN
STOCK 4025 177
Mar-10-09 150
Mar-12-09 600 75
Mar-13-09 75
Mar-16-09 375
Mar-20-09 450 75
Grand Total 4025 177 1650 150


I hope those figures will remain intact and won't be all over the place
once
this is posted. I want to insert two fields that will calculat a
decreasing
balance of the clear and the green. Results of those two fields based on
the
pivot table above would be:

Clear Balance Green Balance
3875 177
3275 102
3200 102
2825 102
2375 27

Hope I haven't asked too much. Hope this will post visibly to me, and
hope
the tables will post without running all over the screen. My apologies if
they do and I won't blame anyone for not answering. Thank you. Connie

It amazes me that you were able to figure out how my pivot table was laid
out. I see that after I posted it, it went all over the place. It looks
nothing like when I posted it. Is there a secret to posting proper tables in
this newsgroup??? Anyway, I followed all your instructions and everything
works like a charm except for the -1 part. No matter where I copy -1 from,
when I do the multiply from paste special I get this response: "You cannot
change, move a part of, or insert cells in a Pivot Table report.....ETC." I
need a decreasing balance, not an increasing one. Up to that point, this
works beautifully. Now just to get the running total to decrease. Any
ideas? Connie

"Roger Govier" wrote:

Hi Connie

I think I would try to tackle it a slightly different way - if you have that
option.
I would have my source data as
Date Clear Green
Stock 4025 177
Mar 10 -150
Mar 12 -600 -75
Mar 13 -75
Mar 16 -375 -75
Mar 20 -450

Then in Pivot Table Layout
Drag Date to Row area
Drag Clear to Data area
Drag Clear to Data area again
Drag Green to Data area
Drag Green to Data area again

Ensure all are set to Sum
On Clear2 and Green 2OptionsShow data asRunning Total inBase fieldDate

Change the Titles form Sum of Clear and Sum of Clear2 to Clear Items and
Clear Balance or whatever terminology suits you.

If you need to convert your existing data to make the daily transactions
negative, just enter -1 in a spare cellCopy that cellSelect the range of
transactions in D and EPaste SpecialMultiply.
--
Regards
Roger Govier

"Connie Martin" wrote in message
...
Sometimes when I post a question here I never see it, but one time when
Googling the subject I found my question posted in this newgroup, but when
I
go through the Microsoft website, it's not visible there. I posted a
question this morning about where to find the option to insert a
calculated
field, and that question has never appeared here. I did find the answer
to
it, however. I have another question now, and hope this will show up.

I have this pivot table:
Requested Ship Date CLEAR STOCK GREEN STOCK CLEAR GREEN
STOCK 4025 177
Mar-10-09 150
Mar-12-09 600 75
Mar-13-09 75
Mar-16-09 375
Mar-20-09 450 75
Grand Total 4025 177 1650 150


I hope those figures will remain intact and won't be all over the place
once
this is posted. I want to insert two fields that will calculat a
decreasing
balance of the clear and the green. Results of those two fields based on
the
pivot table above would be:

Clear Balance Green Balance
3875 177
3275 102
3200 102
2825 102
2375 27

Hope I haven't asked too much. Hope this will post visibly to me, and
hope
the tables will post without running all over the screen. My apologies if
they do and I won't blame anyone for not answering. Thank you. Connie

Hi Connie

No, you cannot change any values in a Pivot Table.
What I was suggesting, was a way of changing your SOURCE data, by Paste
SpecialMultiply.
The Initial Stock values would still be positive, but all usage of product
would be negative, then you will get the results you require.

is there a secret to posting proper tables in this newsgroup???
No, there isn't.
Because I have been using PT's for a long while (and with the table showing
the results you wanted to see), I was able to figure out what your Source
data must have looked like.

The sample data I posted back was typed, with Tabs between the the columns.
Copying and pasting data will almost invariably get "mashed" like yours.
--
Regards
Roger Govier

"Connie Martin" wrote in message
...
It amazes me that you were able to figure out how my pivot table was laid
out. I see that after I posted it, it went all over the place. It looks
nothing like when I posted it. Is there a secret to posting proper tables
in
this newsgroup??? Anyway, I followed all your instructions and everything
works like a charm except for the -1 part. No matter where I copy -1
from,
when I do the multiply from paste special I get this response: "You
cannot
change, move a part of, or insert cells in a Pivot Table report.....ETC."
I
need a decreasing balance, not an increasing one. Up to that point, this
works beautifully. Now just to get the running total to decrease. Any
ideas? Connie

"Roger Govier" wrote:

Hi Connie

I think I would try to tackle it a slightly different way - if you have
that
option.
I would have my source data as
Date Clear Green
Stock 4025 177
Mar 10 -150
Mar 12 -600 -75
Mar 13 -75
Mar 16 -375 -75
Mar 20 -450

Then in Pivot Table Layout
Drag Date to Row area
Drag Clear to Data area
Drag Clear to Data area again
Drag Green to Data area
Drag Green to Data area again

Ensure all are set to Sum
On Clear2 and Green 2OptionsShow data asRunning Total inBase
fieldDate

Change the Titles form Sum of Clear and Sum of Clear2 to Clear Items and
Clear Balance or whatever terminology suits you.

If you need to convert your existing data to make the daily transactions
negative, just enter -1 in a spare cellCopy that cellSelect the range
of
transactions in D and EPaste SpecialMultiply.
--
Regards
Roger Govier

"Connie Martin" wrote in message
...
Sometimes when I post a question here I never see it, but one time when
Googling the subject I found my question posted in this newgroup, but
when
I
go through the Microsoft website, it's not visible there. I posted a
question this morning about where to find the option to insert a
calculated
field, and that question has never appeared here. I did find the
answer
to
it, however. I have another question now, and hope this will show up.

I have this pivot table:
Requested Ship Date CLEAR STOCK GREEN STOCK CLEAR GREEN
STOCK 4025 177
Mar-10-09 150
Mar-12-09 600 75
Mar-13-09 75
Mar-16-09 375
Mar-20-09 450 75
Grand Total 4025 177 1650 150


I hope those figures will remain intact and won't be all over the place
once
this is posted. I want to insert two fields that will calculat a
decreasing
balance of the clear and the green. Results of those two fields based
on
the
pivot table above would be:

Clear Balance Green Balance
3875 177
3275 102
3200 102
2825 102
2375 27

Hope I haven't asked too much. Hope this will post visibly to me, and
hope
the tables will post without running all over the screen. My apologies
if
they do and I won't blame anyone for not answering. Thank you.
Connie

Yes, I finally realized that! Duh! I should've known that!! I did change
the source data columns to negative and formatted the pivot table to red
negative numbers. Somehow I like the look of that better than the minus sign
or the brackets. Thank you again for your help. I have printed this post
and keeping in my binder of Excel "helps". Connie

"Roger Govier" wrote:

Hi Connie

No, you cannot change any values in a Pivot Table.
What I was suggesting, was a way of changing your SOURCE data, by Paste
SpecialMultiply.
The Initial Stock values would still be positive, but all usage of product
would be negative, then you will get the results you require.

is there a secret to posting proper tables in this newsgroup???
No, there isn't.
Because I have been using PT's for a long while (and with the table showing
the results you wanted to see), I was able to figure out what your Source
data must have looked like.

The sample data I posted back was typed, with Tabs between the the columns.
Copying and pasting data will almost invariably get "mashed" like yours.
--
Regards
Roger Govier

"Connie Martin" wrote in message
...
It amazes me that you were able to figure out how my pivot table was laid
out. I see that after I posted it, it went all over the place. It looks
nothing like when I posted it. Is there a secret to posting proper tables
in
this newsgroup??? Anyway, I followed all your instructions and everything
works like a charm except for the -1 part. No matter where I copy -1
from,
when I do the multiply from paste special I get this response: "You
cannot
change, move a part of, or insert cells in a Pivot Table report.....ETC."
I
need a decreasing balance, not an increasing one. Up to that point, this
works beautifully. Now just to get the running total to decrease. Any
ideas? Connie

"Roger Govier" wrote:

Hi Connie

I think I would try to tackle it a slightly different way - if you have
that
option.
I would have my source data as
Date Clear Green
Stock 4025 177
Mar 10 -150
Mar 12 -600 -75
Mar 13 -75
Mar 16 -375 -75
Mar 20 -450

Then in Pivot Table Layout
Drag Date to Row area
Drag Clear to Data area
Drag Clear to Data area again
Drag Green to Data area
Drag Green to Data area again

Ensure all are set to Sum
On Clear2 and Green 2OptionsShow data asRunning Total inBase
fieldDate

Change the Titles form Sum of Clear and Sum of Clear2 to Clear Items and
Clear Balance or whatever terminology suits you.

If you need to convert your existing data to make the daily transactions
negative, just enter -1 in a spare cellCopy that cellSelect the range
of
transactions in D and EPaste SpecialMultiply.
--
Regards
Roger Govier

"Connie Martin" wrote in message
...
Sometimes when I post a question here I never see it, but one time when
Googling the subject I found my question posted in this newgroup, but
when
I
go through the Microsoft website, it's not visible there. I posted a
question this morning about where to find the option to insert a
calculated
field, and that question has never appeared here. I did find the
answer
to
it, however. I have another question now, and hope this will show up.

I have this pivot table:
Requested Ship Date CLEAR STOCK GREEN STOCK CLEAR GREEN
STOCK 4025 177
Mar-10-09 150
Mar-12-09 600 75
Mar-13-09 75
Mar-16-09 375
Mar-20-09 450 75
Grand Total 4025 177 1650 150


I hope those figures will remain intact and won't be all over the place
once
this is posted. I want to insert two fields that will calculat a
decreasing
balance of the clear and the green. Results of those two fields based
on
the
pivot table above would be:

Clear Balance Green Balance
3875 177
3275 102
3200 102
2825 102
2375 27

Hope I haven't asked too much. Hope this will post visibly to me, and
hope
the tables will post without running all over the screen. My apologies
if
they do and I won't blame anyone for not answering. Thank you.
Connie