Hideki,

Have you tried Subtotals (Data Subtotals...) and Grouping? It sound
like that will accomplish what you are trying to do.

Hope This Helps,

Conan Kelly



"hideki" wrote
in message ...

Hi, I'm stuck on how to calculate sum within hierarchy. Below is
what
I'd like to archive. How can I create a macro that in the end will
WRITE A FORMULA into the cells for every level? For example, at
Level
0, the formula is sum all Level1, at level1 = sum all level2 and so
on.
The levels are not limited to 4 but will change dynamically.

I'd tried to looking for a start and end row for each level and use
it
in a sum formula at the upper level. But I got confuse myself.
Nothing's working. Any ideas are very appreciated.

1. L0 = SUM (ALL L1)
2. --L1 = SUM(ALL L2)
3. ---L2 = SUM (ALL L31)
4. ----L31 = SUM (ALL L41)
5. -----L41 10
6. -----L41 26
7. -----L41 33
8. ----L31 = SUM (ALL L42)
9. -----L42 77
10.-----L42 25
11.-----L42 15
12.---L2 = SUM (ALL L32)
13.----L32 = SUM (ALL L43)
14.-----L43 11
15.-----L43 55
16.----L32 = SUM (ALL L44)
17.-----L44 4
18.-----L44 78

Please help,
hideki


--
hideki
------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=515368