So at work I had to keep recreating these summaries. Was a very tedious process so when I had some downtime I was able to create a little program in Excel that would spit out a days worth of work in 5mins. Now that I have that working, I am trying to make it accept user input and be more dynamic.
The general idea is the user selects fields they want summarized and then selects the fields they want totaled up.
__________________Sum of Widgets_____Count of Foo
Level 1a_________________100____________10
____Level 2a _____________35____________ 7
____Level 2b _____________65_____________3
Level 1b
____Level 2c...
Grand Total....
The way i have it now can only go down two levels without getting messy and confusing. I take the calculated values and store them in a 3D array. The problem arises when trying to add more levels, and more dimensions makes my mouth foam.
If any of this makes any sense, is there an easier way to accomplish this?
I have already figured a way to dynamically increase the number of fields going horizontally(summation columns) using an object.
I am using Excel 2007 and would appreciate the advice.