Can't get the desired summary (table, pivot table, ???)

[jhren]

I have a worksheet that's to be part of a workbook for electrical load calculations per NEC (National Electrical Code). Its for general lighting loads by distribution panel, type of occupancy, etc. I'm wanting to generate a two-column range (text cell and an adjacent number cell) that reports similar to the following:

Service
Noncontinuous General Lighting
Dwelling units - 10,000 ft² @ 3 VA/ft² 30,000 VA
Office buildings - 4,000 ft² @ 3.5 VA/ft² 14,000 VA
Storage spaces - 4,000 ft² @ 0.25 VA/ft² 1,000 VA

Continuous General Lighting
Halls, corridors, closets, stairways - 800 ft² @ 0.5 VA/ft² 400 VA
Office buildings - 4,000 ft² @ 3.5 VA/ft² 14,000 VA
Stores - 4,000 ft² @ 3 VA/ft² 12,000 VA

I've arranged all the data via a Table, and "summarize" via a PivotTable, then generate the text from the pivot table. The problem I'm having is one of subsets.... The above example is the text I want for the Service. However, the Service consists of two panels (MDP1 & MDP2), so I need a report which has Service, MDP1, & MDP2. All the loads that are to be reported for MDP1 & MDP2 are included in the loads reported for Service. Also, MDP1 & MDP2 both supply lighting loads directly and subpanels... and those supply sub-subpanels and can also supply lighting loads directly.

I want to generate a summary similar to the above Service example for every panel. I can do that currently one panel at a time, but not all at once. You'll understand better (I hope) by viewing the file, which I'm attaching (if I can; first post here so I don't know yet). Eventually, I want to be able to link the summary to another worksheet which combines all the various calculations. This worksheet is just for one section of the Code.

Can any of you help me..???...!!!

Ohhh BTW, I'm macro/VBA ignorant, and want to avoid VBA as much as possible. I managed to get my pivot table to automatically refresh by gleaning some code off the net... but that's about as savvy as I get

[jhren]

Figured it out.

For those who had a look see, am attaching updated file...

The basic concept to my solution was to create another table with the first column listing individual entries in four columns of source data, then replicate the remaining row data.

Am still interested in an easier, cleaner solution if anyone can think one up...