Insert/Delete rows across worksheets

[jeffreybrown]

I’ve received help with this formula here before and it works great until I insert rows.
On sheet 1, double click on a cell in column A to insert rows. Just for testing insert has been set to 2 rows.

The adding of the rows works fine, but it causes a circular reference in column AD. I noticed the formula starting in AD8 was not copying right so I added to the code to take the formula in AD8 and copy it down which seems to work fine, but still, a circular reference is generated.

If I add new data to column AC the formula updates correctly, but the trouble comes from the cells highlighted in grey on Sheet 2 and 3.

The data in AD is later used to populate a chart so I need the roll up in AD8 and AD14 so I can chart by the Major category. Of course this is much larger and this is only a test.
Any thoughts?