Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot table question | Charts and Charting in Excel | |||
Pivot table question | Excel Discussion (Misc queries) | |||
Pivot Table question | Excel Discussion (Misc queries) | |||
Pivot Table Question : If statment in Pivot Table?? | Excel Discussion (Misc queries) |