Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a row to a subtotalled range
I have a spreadsheet that contains sales values for each month. As there can be more than one value for a month I am using the subtotal function to aggregate the sales to give me twelve values (one for each month). The spreadsheet is maintained on an ongoing basis, so new entries are added from time to time. The data range also has an auto filter so that I can select the records for any particular month if needed.
Currently, February only has one record. After applying the subtotal function to the range, I have added a second record for February, but the subtotal does not update. I am adding the second record by inserting a row and entering the values. However, the subtotal does not update itself irrespective of whether I add the row before or after the existing record. If a month contains more than one record to begin with, I can insert a third row in between the two existing records and the subtotal will update automatically. The problem is specific to only having one record when the subtotal is set up in the first instance. Example: Month Sales($) Jan 100 Jan 150 Jan 100 Jan Total 350 Feb 250 Feb Total 250 Mar 120 Mar 140 Mar Total 260 In the above example, The Feb Subtotal will not automatically update no matter where I insert a second record for Feb. Help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a row to a subtotalled range
Mickey
when you add subtotals it generates a formula, something like: =SUBTOTAL(9,B6:B6) Note that this includes the range that is being subtotalled. So, adding a row above or below won't change that formula. You'll need to remove the subtotals and add them again. In code Sub Macro1() Selection.RemoveSubtotal Selection.Subtotal GroupBy:=1, _ Function:=xlSum, _ TotalList:=Array(2), _ Replace:=True, _ PageBreaks:=False, _ SummaryBelowData:=True End Sub This was generated by recording the manual actions. Regards Trevor "Mickey Mouse" <@ wrote in message ... I have a spreadsheet that contains sales values for each month. As there can be more than one value for a month I am using the subtotal function to aggregate the sales to give me twelve values (one for each month). The spreadsheet is maintained on an ongoing basis, so new entries are added from time to time. The data range also has an auto filter so that I can select the records for any particular month if needed. Currently, February only has one record. After applying the subtotal function to the range, I have added a second record for February, but the subtotal does not update. I am adding the second record by inserting a row and entering the values. However, the subtotal does not update itself irrespective of whether I add the row before or after the existing record. If a month contains more than one record to begin with, I can insert a third row in between the two existing records and the subtotal will update automatically. The problem is specific to only having one record when the subtotal is set up in the first instance. Example: Month Sales($) Jan 100 Jan 150 Jan 100 Jan Total 350 Feb 250 Feb Total 250 Mar 120 Mar 140 Mar Total 260 In the above example, The Feb Subtotal will not automatically update no matter where I insert a second record for Feb. Help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a row to a subtotalled range
One of the things that Excel's Subtotal function (Spreadsheet side from the
Data menu) is doing, setting up ranges for each of the sub totals, and given how formula works with ranges, if you insert a row within the range, the formula will adjust automatically, but if you don't insert within the range, it won't automatically adjust, thus with your 1 row formula range, it would not be possible for it to automatically adjust cause the row above and the row below are both outside of the 1 row range. Given that, you will need to manually adjust the subtotal formula to include both rows. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Mickey Mouse" <@ wrote in message ... I have a spreadsheet that contains sales values for each month. As there can be more than one value for a month I am using the subtotal function to aggregate the sales to give me twelve values (one for each month). The spreadsheet is maintained on an ongoing basis, so new entries are added from time to time. The data range also has an auto filter so that I can select the records for any particular month if needed. Currently, February only has one record. After applying the subtotal function to the range, I have added a second record for February, but the subtotal does not update. I am adding the second record by inserting a row and entering the values. However, the subtotal does not update itself irrespective of whether I add the row before or after the existing record. If a month contains more than one record to begin with, I can insert a third row in between the two existing records and the subtotal will update automatically. The problem is specific to only having one record when the subtotal is set up in the first instance. Example: Month Sales($) Jan 100 Jan 150 Jan 100 Jan Total 350 Feb 250 Feb Total 250 Mar 120 Mar 140 Mar Total 260 In the above example, The Feb Subtotal will not automatically update no matter where I insert a second record for Feb. Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a range | Excel Discussion (Misc queries) | |||
Trouble sorting a subtotalled list | Excel Discussion (Misc queries) | |||
Printing a subtotalled worksheet | Excel Discussion (Misc queries) | |||
Sort a Subtotalled List? | Excel Discussion (Misc queries) | |||
How to copy subtotalled cells to a new worksheet (in a macro), wi. | Excel Discussion (Misc queries) |