The customer had a very specific format for an automated report.
The report uses remote automation from MSAccess to pull from SQL Server then dynamically create around 400 various tabs.
The VBA Code takes 3 columns that are sorted.
Column 1 creates a Header on Top of each change - and a footer (with Subtotal) on bottom - then uses VBA to create group 1
So, the group1 is a list of all the groups with a subtotal.
Problem:
In column 2, the same process takes place. It inserts a Header on top of each group a Footer with Subtotals for each group change in column 2.
Now, the primary Subtotals from Column 1 are:
1. Including both details and SubTotal from group 1
2. Not including the count for the newly created rows (header 1 and subtotal footer row).
Perhaps the data can be collapsed back to group 1 and apply vba to only include the visible rows between the header and footer?
Or, is there a way for SubTotal formulas were suppose to ignore other Subtotal formulas in a range of cells?
The custom format prevents the use of the built in summing and grouping.
GroupA
..detail1 subtotal (in level1 the details and subtotals for these are hidden)
..detail2 subtotal
..detail3 subtotal
SubTotal GroupA
GroupB
..detail1 (in level1 the details and subtotals for these are hidden)
..detail2
.....Detail2 (shown in Level2)
.....Joe
.....Fred
.....Detail 2 Subtotal
..detail3
SubTotal GroupB
The report uses remote automation from MSAccess to pull from SQL Server then dynamically create around 400 various tabs.
The VBA Code takes 3 columns that are sorted.
Column 1 creates a Header on Top of each change - and a footer (with Subtotal) on bottom - then uses VBA to create group 1
So, the group1 is a list of all the groups with a subtotal.
Problem:
In column 2, the same process takes place. It inserts a Header on top of each group a Footer with Subtotals for each group change in column 2.
Now, the primary Subtotals from Column 1 are:
1. Including both details and SubTotal from group 1
2. Not including the count for the newly created rows (header 1 and subtotal footer row).
Perhaps the data can be collapsed back to group 1 and apply vba to only include the visible rows between the header and footer?
Or, is there a way for SubTotal formulas were suppose to ignore other Subtotal formulas in a range of cells?
The custom format prevents the use of the built in summing and grouping.
GroupA
..detail1 subtotal (in level1 the details and subtotals for these are hidden)
..detail2 subtotal
..detail3 subtotal
SubTotal GroupA
GroupB
..detail1 (in level1 the details and subtotals for these are hidden)
..detail2
.....Detail2 (shown in Level2)
.....Joe
.....Fred
.....Detail 2 Subtotal
..detail3
SubTotal GroupB