Well, this group of experts hasn't failed me yet, so here's another puzzler (at least to me). Apologies at the outset if I'm using the wrong terminology for certain things.
Background: I am tracking monthly telephone expenses for users. For any random month, the user belongs to a specific cost center. Since users can move between cost centers, the complete set of data for a particular user for a particular month includes their CC and the $amt they spent.
Spreadsheet layout (columns)
Last | First | CCinJan | $Jan | CCinFeb | $Feb | etc.
The CCinX field is a lookup that matches First and Last against another table (an HR headcount report) to derive the CC the user belonged to that month.
I have a series of pivot tables, one for each month, that I supply to the Finance dept showing the monthly allocation results. Their table basically looks like:
CC1inMonthX [pivot row=CCinJan]
Last1 [pivot row=Last] $amtinMonthX1 [pivot data=$Jan]
Last2 $amtinMonthX2
CC2inMonthX
Last3 $amtinMonthX3
...etc
As currently implemented, the spreadsheet does exactly what I want.
My challenge now is this: I wish to supply each CC Manager with a summary of his/her CC spending, and ONLY his/her CC. I can certainly do this with my existing pivots simply by selecting to display only the CC of choice. However, I would like to be able to provide the CC mgr with an annual view rather than just a monthly view. In other words,
I'd like to be able to present them with something like:
$Jan $Feb $Mar
Last1 amtInJan1 amtinFeb1 amtinMar1
Last2 amtInJan2 amtinFeb2 amtinMar2
...etc
The problem, you might note, is that the data extracted for (e.g.) amtInJan1 should only be extracted for this CC IF CCinJan=currentCC, and for amtInFeb2 only if CCinFeb=currentCC. Therefore, the initial stab of:
CCinJan [pivot row]
Last [pivot row]
amtInJan [pivot data]
amtinFeb [pivot data]
amtInMar [pivot data]
obviously fails. It certainly shows all billings for users in CC X in Jan, but the values returned for Feb (e.g.) are returned even if the employee was in a different CC in Feb, etc.
I'm trying to figure out how to have the data elements of my table be correctly selected and displayed in the appropriate columns. It seems to me this is more than can be handled by simple table layout (and I have this nagging feeling that this is still Pivot Table 101 stuff, but consider me still trapped in the Survey for Non-Majors course <g>)
Any ideas?
Background: I am tracking monthly telephone expenses for users. For any random month, the user belongs to a specific cost center. Since users can move between cost centers, the complete set of data for a particular user for a particular month includes their CC and the $amt they spent.
Spreadsheet layout (columns)
Last | First | CCinJan | $Jan | CCinFeb | $Feb | etc.
The CCinX field is a lookup that matches First and Last against another table (an HR headcount report) to derive the CC the user belonged to that month.
I have a series of pivot tables, one for each month, that I supply to the Finance dept showing the monthly allocation results. Their table basically looks like:
CC1inMonthX [pivot row=CCinJan]
Last1 [pivot row=Last] $amtinMonthX1 [pivot data=$Jan]
Last2 $amtinMonthX2
CC2inMonthX
Last3 $amtinMonthX3
...etc
As currently implemented, the spreadsheet does exactly what I want.
My challenge now is this: I wish to supply each CC Manager with a summary of his/her CC spending, and ONLY his/her CC. I can certainly do this with my existing pivots simply by selecting to display only the CC of choice. However, I would like to be able to provide the CC mgr with an annual view rather than just a monthly view. In other words,
I'd like to be able to present them with something like:
$Jan $Feb $Mar
Last1 amtInJan1 amtinFeb1 amtinMar1
Last2 amtInJan2 amtinFeb2 amtinMar2
...etc
The problem, you might note, is that the data extracted for (e.g.) amtInJan1 should only be extracted for this CC IF CCinJan=currentCC, and for amtInFeb2 only if CCinFeb=currentCC. Therefore, the initial stab of:
CCinJan [pivot row]
Last [pivot row]
amtInJan [pivot data]
amtinFeb [pivot data]
amtInMar [pivot data]
obviously fails. It certainly shows all billings for users in CC X in Jan, but the values returned for Feb (e.g.) are returned even if the employee was in a different CC in Feb, etc.
I'm trying to figure out how to have the data elements of my table be correctly selected and displayed in the appropriate columns. It seems to me this is more than can be handled by simple table layout (and I have this nagging feeling that this is still Pivot Table 101 stuff, but consider me still trapped in the Survey for Non-Majors course <g>)
Any ideas?
