Need help in generating a report.

[lukus2005]

I have a spreadsheet (Sheet1) that summarizes all of my contracts.
Column A contains the name of my clients for whom I do several jobs
for. Column B contains the name of the contract. Column C contains a
flag to indicate the job has been completed. Column F contains the
profit/loss amount for that job. I only have a handful of clients but
several contracts with each.

On Sheet 2, I would like to have a report that sorts all of my
contracts by clients, along with the name, amount each one made or
lost, and then have a total for each client.

The report should look something similar to this...

ACME Inc.
Contract #1 $ 3,000
Contract #2 $ 2,500
Contract #3 -$ 1,000
Total: $ 4,500

XYZ Inc.
Contract #1 $ 1,000
Contract #2 $ 6,500
Contract #3 $ 1,500
Total: $ 9,000

I am not familiar with macros and my understanding of formulas are at
least at an intermediate level. Any help would be appreciated.
Thanks.

 

[jadeB]

It looks like you need a pivot table.

Make sure that each of your contracts has a client name in that row.

If you are using Excel 2007 go to insert - pivot table and choose your
data range. You'll want to have Contract and then Client columns in
"Row Labels" and your number data in Values.

Go to the design tab and insert subtotals

Hope this helps.

 

[Shane Devenshire]

Hi,

The easiest way is to use a Pivot Table. Select the range of data and
choose Data, PivotTable and PivotChart Report, click Next twice and on the
3rd step of the wizard click Layout, drag the Client field button to the Row
area and then the Contract button below it in the Row area, and then the
Cost/Amount field to the Data area. Click Next, choose New Worksheet and
click finish.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[anandydr]

Yes, surely the easiest way would be to use a pivot table. In Excel
2007 you will find it in Insert ribbon. Make sure you have column
names like Name of Company, Name of Contract, etc. As pivot table
works on columns only if the name of column is absent it won't work.

HTH,
Anand

 

[vbaexcel]

HI

Discover the most powerful function in Excel: SUMPRODUCT:

http://excel-examples.com/excel-3-7-formula-sumproduct.htm

If 5% of Excel users knew about SUMPRODUCT the face of business
reporting would change.

 

[lukus2005]

Yes, surely the easiest way would be to use a pivot table. In Excel
2007 you will find it in Insert ribbon. Make sure you have column
names like Name of Company, Name of Contract, etc. As pivot table
works on columns only if the name of column is absent it won't work.

HTH,
Anand

Well I tried creating a PivotTable but I am getting the following
error...

The PivotTable field is not valid. To create a PivotTable report, you
must use data that is organised with labeled columns.

Now all of my columns are uniquely labeled so I don't know why I get
that error. I tried including, and excluding the column headers in my
PivotTable range but it doesn't accept it either way.

 

[lukus2005]

Well I tried creating a PivotTable but I am getting the following
error...

The PivotTable field is not valid. To create a PivotTable report, you
must use data that is organised with labeled columns.

Now all of my columns are uniquely labeled so I don't know why I get
that error. I tried including, and excluding the column headers in my
PivotTable range but it doesn't accept it either way.

Only other thing I can think of is that I do not have row headers in
my table, just column headers. If row headers are required, then
PivotTable is not what I need.