Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Hi, This is my first post so I hope it is in the right forum. I have a spreadsheet with subtotals on and we have a problem inserting new rows. I thought that when a row was inserted above the subtotal row, the subtotal would update automatically - but it doesn't. Basically, we need to add news rows on a regular basis above the subtotal row *and *update the subtotal. This is what we want to happen - Before 2 3 4 0 2 5 2 2 0 4 7 9 (Subtotal) After 2 3 4 0 2 5 2 2 0 0 0 2 (new row) 4 7 11 (Subtotal) Hope this makes sense! How can we get the subtotals to update each time a new is inserted above them? Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Insert a blank row above the subtotals and recaclulate them. Reduce the
height of the blank row if you wish. When inserting a new row, insert it ABOVE the blank row. Then it will be automatically picked up in the calculations. Vaya con Dios, Chuck, CABGx3 "Alison1016" wrote: Hi, This is my first post so I hope it is in the right forum. I have a spreadsheet with subtotals on and we have a problem inserting new rows. I thought that when a row was inserted above the subtotal row, the subtotal would update automatically - but it doesn't. Basically, we need to add news rows on a regular basis above the subtotal row *and *update the subtotal. This is what we want to happen - Before 2 3 4 0 2 5 2 2 0 4 7 9 (Subtotal) After 2 3 4 0 2 5 2 2 0 0 0 2 (new row) 4 7 11 (Subtotal) Hope this makes sense! How can we get the subtotals to update each time a new is inserted above them? Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Hi, Thank you for your reply. We had thought about something similar. The problem is there are 4 spreadsheets each with at least 35 worksheets already set up with data, so this would be a huge task. Also, the data will be maintained by a large number of people, so we need an easy way for them to add rows so they don't cause a problem with the subtotals. The normal/logical way for them to do this would be to click on the subtotal row to insert a new row between the last one and the subtotal row - but the same problem would arise. Is there anything else we could try? I understand now why so many people don't use the subtotal function. If Excel knows where the header row is and where the subtotal rows are, why can't the subtotals be refreshed when a new row is inserted above the subtotal row in the same way it does if you insert a row in the middle of a set of rows? Frustrated... Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Hi Alison1016
It's a project, and a bit beyond the scope for this forum, but to give you an example of what can be done with macros, I have a 15000 record database that is added to daily. Yet, with macros, I can on demand produce any one of several "reports" on that data, sorted, filtered, and subtotaled to my desire. It "always" gets the proper number of rows into the calculations, regardless of how many were added or discarded. Talk to your local Excel-Guru, perhaps (s)he can do something for you, or you can always contact the frequent responders to this newsgroup and one of them will no-doubt be happy to help on a consulting basis. And, if you have more specific questions, feel free to post back here. hth Vaya con Dios, Chuck, CABGx3 "Alison1016" wrote: Hi, Thank you for your reply. We had thought about something similar. The problem is there are 4 spreadsheets each with at least 35 worksheets already set up with data, so this would be a huge task. Also, the data will be maintained by a large number of people, so we need an easy way for them to add rows so they don't cause a problem with the subtotals. The normal/logical way for them to do this would be to click on the subtotal row to insert a new row between the last one and the subtotal row - but the same problem would arise. Is there anything else we could try? I understand now why so many people don't use the subtotal function. If Excel knows where the header row is and where the subtotal rows are, why can't the subtotals be refreshed when a new row is inserted above the subtotal row in the same way it does if you insert a row in the middle of a set of rows? Frustrated... Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Thank you very much for your help. Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Alison,
I always find it better to have subtotals (or totals) on the top line of a spreadsheet, which can then be fixed in place by using Window | Freeze panes. The total is then always visible, even if you scroll down the sheet. Another advantage, though is that the sum( ) range (or subtotal(9, ) range) can then be made much larger than the data occupies (even up to row 65536 if you want), to allow a User to add new data without having to bother about inserting rows. You might like to apply this approach to your existing files. Hope this helps. Pete |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Hi Alison,
In Excel 2000 a feature was introduced to get around this exact problem. (Tools Options)(Edit tab, "Extend list format and formulas") in 2000, 2002 or (Tools Options)(Edit tab, "Extend data range formats and formulas") in 2003 When this option is enabled you can insert additional rows and column at the bottom or on the right of cells which are referenced by a formula and the formula will be expanded automatically. regards |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Hi Pete, That makes sense - I'll give it a try. Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Thank you for your reply. I got really excited but - the box is already ticked. Is there something that could be preventing this option from working correctly? Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Newbie post - help with Subtotal please!
Hi again, Whilst trying to find out more about "Extend data range formats and formulas" I found this on the Microsoft website "In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows." We haven't got five rows of data between each subtotal yet (and may never) - is that why it doesn't work? Kind regards, Alison -- Alison1016 ------------------------------------------------------------------------ Alison1016's Profile: http://www.excelforum.com/member.php...o&userid=34563 View this thread: http://www.excelforum.com/showthread...hreadid=543241 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUBTOTAL - TJ | Excel Worksheet Functions | |||
subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
Subtotal Bug in Excel 2003 | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |