Newbie post - help with Subtotal please!

[Alison1016]

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

 

[Guest]

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]

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

 

[Guest]

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]

Thank you very much for your help.

Kind regards,

Alison

 

[Pete_UK]

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

 

[Guest]

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

 

[Alison1016]

Hi Pete,

That makes sense - I'll give it a try.

Kind regards,

Alison

 

[Alison1016]

Thank you for your reply. I got really excited but - the box is alread
ticked. Is there something that could be preventing this option fro
working correctly?

Kind regards,

Aliso

 

[Alison1016]

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