Pivot Table Sum by Employee Name

[jp401]

Does anyone know if this is possible or of any tricks to get what I need?

I have a list of org codes and people that hold certain responsibilities on the org code. I want to create a pivot table that displays the org code in a row, the reponsibility code as a column and then the person's name under the responsibilty that thye hold. I have not been able to get this to work since the it looks like the value areas only is numeric. Any ideas? Thank you.


Org Code______Reponsibility Code A__________Reponsibility Code A


00000001_____Joe Smith______________________Mary Jones__________

00000002_____Jim Black______________________Sue Nelson__________

 

[Faseeh]

Hi jp401,


Why you want to place "Person's name" in the Values Section?? This area normally gets numeric values from "list" that is summed under various heading? And if somehow you get these names in there, how will you sum up Numeric Characters?


What i can understand is that you want a table (not necessarily a pivot table) that lists Org Code to the Left and Responsibility Code on the top while body of table will constitute Person's name? Isn't it so?


Regards,

FASEEH

 

[jp401]

Yes, that is what I want. I have been using pivot tables so much, I did not think of tables. Is it possible to create a table that reads from the data sheet in my Excel file? Maybe I need to do a little research on tables. Can you point me in the right direction? Thanks

 

[Faseeh]

Hi jp401,


I have worked out a sheet that match your requirement. If your data i.e. Org Code, Resp. Code & Name are located in A3:D23 (incl. Headers) and Your Table in Located in D3:T23 (incl. Left most Job Code Column and Top row for Org Code) following formulas will work:


For Job Code:

=IFERROR(INDEX($B$4:$B$23,MATCH(0,COUNTIF($D$3:D3,$B$4:$B$23),0)),"N") Enter with Ctrl+Shift+Enter

Enter in D4 & Drag Down...


For Resp. Code:

=INDEX($A$4:$A$23,MATCH(0,COUNTIF($D$3:D3,$A$4:$A$23),0)) Enter with Ctrl+Shift+Enter

Enter in E3 and Drag to the left...


For Range E4:T23 (For Names)

=IF($D4="N","-",IFERROR(OFFSET($C$1,SUMPRODUCT((($B$4:$B$23)=$D4)*((($A$4:$A$23)=E$3)),ROW($B$4:$B$23))-1,0),"..."))

Enter in E4 and Drag to left and down....


Hope it will help you..


One thing important can impair the working of the sheet and that is when you have two similar entries of Org Code and Resp. Code with two diff. Names then it will show you zero because there will be two names to be displayed that will cause this error. Am working to overcome this too.


Further that "..." Show the cell had an error msg (no name to be displayed), "--" shows that Org Code was not available therefore cells are empty.


Kindly check and give feedback. Here is the file:

http://www.2shared.com/file/ijnubWma/Copy_of_Pivot_Table_Problem.html


Regards,

FASEEH