Thread: Compile data by unique criteria

I've created a spreadsheet that itemizes employee payroll and class codes. I would like to compile the total payroll for each class code on a separate worksheet. For ex:

sheet 1
EMPLOYEE CODE PAYROLL
DAVIS, LOREEN 9107 $41,000
FISHER, JOHN 8047 $66,000
JONES, DIANE 8810 $32,000
RYAN, GARY 9107 $28,000
SMITH, JOE 8810 $27,000
WOODS, KAREN 8047 $52,000

sheet 2
CODE PAYROLL
8810 $59,000
9107 $69,000
8047 $118,000

I know how to reference another worksheet, but not sure how to compile all payroll from a unique class code into a separate line of data.

Any ideas?

Thank you,

Heather

Thank you for your help with this. I can't figure out how the pivot table is pulling data from the Data worksheet. When I change the values in the Data worksheet, the pivot table values do not change, and I can't find any formulas associated with the Pivot Table data fields.

I'll have to read up on Pivot Tables, I've never used one. But thanks for pointing me in the right direction.

If you change data in the data source, then you will need to refresh the pivot table to reflect the changes. This could be automated with VBA (using a Worksheet_Change or Worksheet_Activate event, for example, if required).

I have attached a new workbook with some notes on creating and updating the pivot table. I have also made use of a dynamic named range which will accommodate additions to the pivot table data source.

A useful link:
http://peltiertech.com/Excel/Pivots/pivotstart.htm