How to turn data from an Excel Spreadsheet into a report by dept.

[SarahT413]

I have expenses listed in a spreadsheet and I want to separate the data into
departments in order to give a monthly expense status report to each
department head?

 

[Bill Ridgeway]

I have expenses listed in a spreadsheet and I want to separate the data
into
departments in order to give a monthly expense status report to each
department head?

Another job for Pivot Table!! Have a look at <Data><Pivot Table and Pivot
Chart Report>.

Bill Ridgeway
Computer Solutions

 

[SarahT413]

Thanks, Bill,

But, I've already tried Pivot Tables and Pivot Chart Reports, and it tells
me that the Pivot Table Field Name is not valid and that I need to use data
that is organized as a list with labeled columns (it is). I have tried to
trouble-shoot Pivot Tables to no avail.

 

[DannyS]

Why not use SUMIF?

SUMIF(A:A,"Sales",B:B)

If departments are in col A and costs in col B

 

[SarahT413]

So, I just fooled around with your "SUMIF" code, and realized what it does,
it adds Amounts from only a certain department, but I'm still clueless as to
how to incorporate this into a formatted report by department?

 

[Gord Dibben]

Have you tried Data>Filter>Autofilter to isolate by dept.?

You can copy the visible cells to another sheet or create a Custom View of the
the results of your filter.

Also have a look at the SUBTOTAL function for summing expenses for each dept.
filtered.


Gord Dibben MS Excel MVP

 

[JP]

Did you select a cell inside the data source before trying to create
the PT?


HTH,
JP