Subtotals

[Gary]

I have 3 columns:
Month
Product
Hours

I want to be able to group months in their respective groups and then find
the subtotal for the hours within each month group. And a Grand Total in the
end.

I am only able to do this once with the DATA / Subtotals sequence. After
that, I find that once i enter a new month in a new row, that row does not
insert itself into the respective month group. Even after I do the DATA /
Subtotals sequence from the menus.
Any suggestions how excel can refresh/update the groupings when a user
enters in a new row a given month and then for it to eventually place it in
the existing respective month group.
thanks.

 

[Dave Peterson]

I'd remove the data|Subtotals
resort the data
reapply the data|subtotals

If you do this a lot, you may want to record a macro when you do it manually.
Then you could just rerun that macro if/when you need to.

Another suggestion that may work better is to invest some time in learning about
pivottables. Depending on what you're doing, it may be the easiest solution.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Gary]

Thanks for the advice.
I did add a macro and its fine.
The problem now is that the Grand Total is not giving the correct figure. It
adds all the hours including the subtotal figure. I either want it to add
all the figures (excluding the subtotals) or it simply sums up all the
subtotals only.

 

[Dave Peterson]

Did you put the grand totals in or did you get them from data|subtotals?

I've never seen data|subtotals insert a formula that does what you say.
=subtotal() is smart enough to ignore other =subtotal()'s.

 

[Gary]

The grand total comes up automatically in data/subtotals.
The grandtotal formula that is given is: =SUBTOTAL(9,C2:C29)

 

[Dave Peterson]

I'd say double check your arithmetic. That looks like a nice formula that will
ignore =subtotal() formulas in C2:C29.

Is that the correct range to subtotal?

The grand total comes up automatically in data/subtotals.
The grandtotal formula that is given is: =SUBTOTAL(9,C2:C29)

 

[Gary]

ok its all working fine now.
When adding data/rows, I have this macro which will refresh the data where
it slots in (date sort) the respective additions in the correct date order
in the Month column (amongst the existing data). It's a case of the macro
removing the subtotal format , then sorting the date data and then
reinstating the subtotal format. This is all fine.
Q1. I find the Grand Total (which is calculating correctly) being pushed
further down the worksheet. As i further use the macro, it is pushing it
even further down with a large section of empty rows above it. I simply want
a few blank rows below the data and then the Grand Total to appear.

Q2. When printing the nested data, the print job gives one section/subtotal
per page and not all subtotals in the one page.

 

[Dave Peterson]

In reverse order (easiest first):

Q2. When use data|subtotals, you'll see an option for "page breaks between
groups". It sounds like you checked that option when you recorded your macro.
Look at your code for "PageBreaks:=True" and change it to false.

Q1. After you remove the subtotals, you let excel guess at what to subtotal.
Since the used range has been extended (by the subtotal rows), excel guesses
that you want those empty rows included in your range.

You could add a step your macro that tries to reset the used range (it may be as
simple as using "Set dummyRng = worksheets("whatever").usedrange".

You can see an example at Debra Dalgleish's site for trying to reset that used
range:
http://contextures.com/xlfaqApp.html#Unused

Personally, I like to be more explicit with my range to subtotal.

For example, if I can use the data in column A to find the last row and the data
in row 1 to find the last column, I'd do something like:

dim myRng as range
dim LastCol as long
dim LastRow as long

with worksheets("whatever")
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
set myrng = .range("a1",.cells(lastrow,lastcol))
end with

myrng.subtotal groupby:.......

ok its all working fine now.
When adding data/rows, I have this macro which will refresh the data where
it slots in (date sort) the respective additions in the correct date order
in the Month column (amongst the existing data). It's a case of the macro
removing the subtotal format , then sorting the date data and then
reinstating the subtotal format. This is all fine.
Q1. I find the Grand Total (which is calculating correctly) being pushed
further down the worksheet. As i further use the macro, it is pushing it
even further down with a large section of empty rows above it. I simply want
a few blank rows below the data and then the Grand Total to appear.

Q2. When printing the nested data, the print job gives one section/subtotal
per page and not all subtotals in the one page.