Greetings all,
in the attached workbook, the two worksheets (T-Attend and T-Eval) contain data imported from another workbook; that part is working fine. What I need to do however, is extrapolate data for monthly reports, with each report being between two specific dates. There are 12 monthly reports to be done, and each report goes from the 22nd of one month to the 21st of the next month (ie: 22 Jan - 21 Feb, 22 Feb - 21 Mar, etc). With some help from others here, I had initially tried a whole bunch of countifs, sumifs and some complicated array formulas, all of which worked but took forever to update (on my fast machine at home, it would take up to 5 minutes to update so I shudder to think how long this would take on the dinosaur of our work PC).
Pivot tables were suggested to me and they seem to be a very powerful tool indeed but I'm struggling with getting a few things to work. On the sheet PivAttend, for example, the first pivot table (JanFeb) works out for me the number of sessions of each type of training as well as the number of attendees for the dates 22 Jan to 21 Feb - so far, so good. When I add another pivot table (FebMar) and try to get it to do the same for the period 22 Feb to 21 Mar, it changes the values in the first pivot table, which is not what I want. Using the 'group' option in the tables doesn't seem to work the way I thought it would.
So, my question is two-fold:
a. How do I set up a pivot table to extrapolate the data for each reporting period?
b. How do I insert a calculation to give me the total sessions and attendees? I tried 'grand totals' but that doesn't seem to do what I want. I looked into (and was confused by) calculated fields and calculated items (the latter being greyed out on my toolbar anyway).
Any help would be greatly appreciated.
Cheers,
AJ
Bookmarks