Insert sum/subtotal formulae to sum values on a list

[Son]

Hello, I have question about how to add rows with subotal and sum formulae in an excel list.
I have a database with names of employees working in various sections. Each Section has subsections who are run by managers and staff is working for them.

In the following example we have the following:
Column A shows the "position code", which is a code assigned to each "position". This code is handwritten, not automated. It is intended to show hierarchy of position.
Column B shows the "position" where each employee works.
Column C shows the count of staff that work in each "position". The actual staff is shown more clearly in column D, where you can see the numbers and the summing formulae.

For example rows 7, 8, 11, 12 all contain a summing formula or a subtotal formula.

In cell A3 I have the value "1", which is shown when I apply filters to the list. Because I use a lot of different filters in various columns (actually the whole list has more than 25 columns), I need the two different formulae in each summing row: ie to subtotal one and the sum one.

So far I have hardcoded each formula in each row. But I need to make this list more dynamic. I cannot use a pivot due to the various filters I need to apply and new data I show using vba.

Important thing to note is that I am using excel 2003 since I have not been able to upgrade my code as it presents many errors. The code also works well in excel 2007 but that's it.
Also, as you can see, my excel version uses " ; " instead of " , ".

I have over 50 modules of code and about 30 worksheets in each workbook. I also have about 15 workbooks, one for each branche of the company, but I need to create more workbooks for the new branches. The eample below is only a fraction of the whole list in the worksheet.

The content of Columns A and B is in a different worksheet and it is different for each branch. I use code to fill Column C (and the other 25+ columns, with different data. For example Column C shows the people working in the company regularly and Column D would normally show the temporary staff, and so on. As I said, the formulae are fixed and hardcoded by hand in each of the rows. My code only updates the cell that do not contain formulae.

I would be very grateful if you had any input on this problem and I'm looking forward to hearing your thoughts. Also, I'd be more than happy to answer any questions.

1	A	B	C	D
2
3	1
4
5
6	POSITION CODE	POSITION	NUMBER OF STAFF	FORMULA
7	1	SECTION 1	2	=IF($A$3<>1;SUBTOTAL(9;D8:D10);(D8))
8	1.0	MANAGER'S 1 OFFICE	2	=IF($A$3<>1;SUBTOTAL(9;D9:D10);SUM(D9:D10))
9	1.0.1	MANAGER 1	1	1
10	1.0.2	ASSISTANT MANAGER	1	1
11	2	SECTION 2	318	=IF($A$3<>1;SUBTOTAL(9;D12:D113);(D12+D16+D18+D24+D30+D36+D59+D81+D86+D95+D102+D105+D109+D112))
12	2.0	MANAGER'S 1 OFFICE	3	=IF($A$3<>1;SUBTOTAL(9;D13:D15);SUM(D13:D15))
13	2.0.1	MANAGER 1	1	1
14	2.0.2	ASSISTANT MANAGER 1	2	2
15	2.0.3	ASSISTANT MANAGER 2	0	0
16	2.1	MANAGER'S 2 OFFICE	5	=IF($A$3<>1;SUBTOTAL(9;D17);SUM(D17))
17	2.1.1	MANAGER 2	5	5
18	2.2	MANAGER'S 3 OFFICE	40	=IF($A$3<>1;SUBTOTAL(9;D19:D23);SUM(D19:D23))
19	2.2.1	MANAGER 3	5	5
20	2.2.2	ASSISTANT 1	10	10
21	2.2.3	ASSISTANT 2	5	5
22	2.2.4	ASSISTANT 3	10	10
23	2.2.5	ASSISTANT 4	10	10

 

[Son]

I forgot to say that I need that the positions are not fixed, there may be other positions inserted or deleted from this list. This is another reason that I would need this list to be dynamic. Any ideas???

 

[Son]

Any ideas??? Please???

 

[Son]

ok, so I'm reposting my question using xl2bb in the hope that it will make it more clear.

So, as I said above, I have a spreadsheet with employees according to their position in the company.
The data contains the code for each hiearchical position, the name of the position, and the number of employees in each hierarchical position.
The grey rows sum the number of employees for each section or department.
The problem is that the formulas in column C (counting the employees according to hierarchy level), are not automatic, I need to input specific ranges for each, by hand.
But, if I change the hierarchal structure, I need to rewrite the formulas by hand.

Is there a way that I can make the formulas automated, eg by using code? Or perhaps I could use summing by colour?
I cannot use a pivot because I need to use the data as a list, as I need to apply various filters.

Any ideas would be most appreciated!!! I'm looking forward to hearing your thouhts!

test.xls
	A	B	C
1
2
3	1	= 1 means I have filters applied
4
5
6	POSITION CODE	POSITION IN HIERARCHY	COUNT STAFF
7	1	SECTION 1	2
8	1.0	MANAGER'S 1 OFFICE	2
9	1.0.1	MANAGER 1	1
10	1.0.2	ASSISTANT MANAGER	1
11	2	SECTION 2	295
12	2.0	MANAGER'S 1 OFFICE	3
13	2.0.1	MANAGER 1	1
14	2.0.2	ASSISTANT MANAGER 1	2
15	2.0.3	ASSISTANT MANAGER 2	0
16	2.1	MANAGER'S 2 OFFICE	5
17	2.1.1	MANAGER 2	5
18	2.2	MANAGER'S 3 OFFICE	40
19	2.2.1	MANAGER 3	5
20	2.2.2	ASSISTANT 1	10
21	2.2.3	ASSISTANT 2	5
22	2.2.4	ASSISTANT 3	10
23	2.2.5	ASSISTANT 4	10
24	2.3	MANAGER'S 4 OFFICE	45
25	2.3.1	MANAGER 4	5
26	2.3.2	ASSISTANT 1	10
27	2.3.3	ASSISTANT 2	10
28	2.3.4	ASSISTANT 3	10
29	2.3.5	ASSISTANT 4	10
30	2.4	MANAGER'S 5 OFFICE	25
31	2.4.1	MANAGER 5	5
32	2.4.2	ASSISTANT 1	5
33	2.4.3	ASSISTANT 2	5
34	2.4.4	ASSISTANT 3	5
35	2.4.5	ASSISTANT 4	5
36	2.5	MANAGER'S 6 OFFICE	78
37	2.5.1	DAY PERSONNEL	14
38	2.5.1.1	MANAGER 6	1
39	2.5.1.2	ASSISTANT 1	3
40	2.5.1.3	ASSISTANT 2	6
41	2.5.1.4	ASSISTANT 3	2
42	2.5.1.5	ASSISTANT 4	0
43	2.5.1.6	ASSISTANT 5	2
44	2.5.2	SHIFT PERSONNEL	64
45	2.5.2.1	SHIFT MANAGER	5
46	2.5.2.2	ASSISTANT 1	5
47	2.5.2.3	ASSISTANT 2	5
48	2.5.2.4	ASSISTANT 3	5
49	2.5.2.5	ASSISTANT 4	5
50	2.5.2.6	ASSISTANT 5	15
51	2.5.2.7	ASSISTANT 6	5
52	2.5.2.8	ASSISTANT 7	5
53	2.5.2.9	ASSISTANT 8	5
54	2.5.2.10	ASSISTANT 9	5
55	2.5.2.11	ASSISTANT 10	0
56	2.5.2.12	ASSISTANT 11	0
57	2.5.2.13	ASSISTANT 12	0
58	2.5.2.14	ASSISTANT 13	4
59	2.6	MANAGER'S 7 OFFICE	48
60	2.6.1	DAY PERSONNEL	13
61	2.6.1.1	MANAGER 7	1
62	2.6.1.2	ASSISTANT 1	3
63	2.6.1.3	ASSISTANT 2	9
64	2.6.1.4	ASSISTANT 3	0
65	2.6.1.5	ASSISTANT 4	0
66	2.6.2	SHIFT PERSONNEL	35
67	2.6.2.1	SHIFT MANAGER	5
68	2.6.2.2	ASSISTANT 1	5
69	2.6.2.3	ASSISTANT 2	5
70	2.6.2.4	ASSISTANT 3	5
71	2.6.2.5	ASSISTANT 4	5
72	2.6.2.6	ASSISTANT 5	5
73	2.6.2.7	ASSISTANT 6	0
74	2.6.2.8	ASSISTANT 7	0
75	2.6.2.9	ASSISTANT 8	5
76	2.6.2.10	ASSISTANT 9	0
77	2.7	MANAGER'S 8 OFFICE	12
78	2.7.1	MANAGER 8	1
79	2.7.2	ASSISTANT 1	1
80	2.7.3	ASSISTANT 2	5
81	2.7.4	ASSISTANT 3	5
82	2.8	MANAGER'S 9 OFFICE	39
83	2.8.1	MANAGER 9	4
84	2.8.2	ASSISTANT 1	5
85	2.8.3	ASSISTANT 2	5
86	2.8.4	ASSISTANT 3	5
87	2.8.5	ASSISTANT 4	5
88	2.8.6	ASSISTANT 5	5
89	2.8.7	ASSISTANT 6	5
90	2.8.8	ASSISTANT 7	5
91	3	SECTION 3	49
92	3.0	MANAGER'S 1 OFFICE	3
93	3.0.1	MANAGER 1	1
94	3.0.2	ASSISTANT 1	2
95	3.0.3	ASSISTANT 2	0
96	3.1	MANAGER'S 2 OFFICE	6
97	3.1.1	MANAGER 2	0
98	3.1.2	ASSISTANT 1	2
99	3.1.3	ASSISTANT 2	4
100	3.1.4	ASSISTANT 3	0
101	3.1.5	ASSISTANT 4	0
102	3.2	MANAGER'S 3 OFFICE	2
103	3.2.1	MANAGER 3	1
104	3.2.2	ASSISTANT 1	1
105	3.3	MANAGER'S 4 OFFICE	38
106	3.3.1	DAY PERSONNEL	3
107	3.3.1.1	MANAGER 4	1
108	3.3.1.2	ASSISTANT 1	2
109	3.3.1.3	ASSISTANT 2	0
110	3.3.2	SHIFT PERSONNEL	35
111	3.3.2.1	SHIFT MANAGER	5
112	3.3.2.2	ASSISTANT 1	15
113	3.3.2.3	ASSISTANT 2	15
114	3.4	MANAGER'S 5 OFFICE	0
115	3.4.1	MANAGER 5	0
116	3.4.2	ASSISTANT 1	0
test (staff)

Cell Formulas
Range	Formula
C7	C7	=IF($A$3<>1,SUBTOTAL(9,C8:C10),(C8))
C8,C114,C102	C8	=IF($A$3<>1,SUBTOTAL(9,C9:C10),SUM(C9:C10))
C11	C11	=IF($A$3<>1,SUBTOTAL(9,C12:C90),(C12+C16+C18+C24+C30+C36+C59+C77+C82))
C12,C110,C106,C92	C12	=IF($A$3<>1,SUBTOTAL(9,C13:C15),SUM(C13:C15))
C16	C16	=IF($A$3<>1,SUBTOTAL(9,C17),SUM(C17))
C18,C96,C60,C30,C24	C18	=IF($A$3<>1,SUBTOTAL(9,C19:C23),SUM(C19:C23))
C36	C36	=IF($A$3<>1,SUBTOTAL(9,C37:C58),(C37+C44))
C37	C37	=IF($A$3<>1,SUBTOTAL(9,C38:C43),SUM(C38:C43))
C44	C44	=IF($A$3<>1,SUBTOTAL(9,C45:C58),SUM(C45:C58))
C59	C59	=IF($A$3<>1,SUBTOTAL(9,C60:C76),(C60+C66))
C66	C66	=IF($A$3<>1,SUBTOTAL(9,C67:C76),SUM(C67:C76))
C77	C77	=IF($A$3<>1,SUBTOTAL(9,C78:C81),SUM(C78:C81))
C82	C82	=IF($A$3<>1,SUBTOTAL(9,C83:C90),SUM(C83:C90))
C91	C91	=IF($A$3<>1,SUBTOTAL(9,C92:C116),(C92+C96+C102+C105+C114))
C105	C105	=IF($A$3<>1,SUBTOTAL(9,C106:C113),(C106+C110))