Need help in generating a report.

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.
0
lukus2005 (35)
11/21/2008 4:58:16 PM
excel 39879 articles. 2 followers. Follow

6 Replies
689 Views

Similar Articles

[PageSpeed] 58

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.


On Nov 21, 10:58=A0am, "lukus2...@gmail.com" <lukus2...@gmail.com>
wrote:
> 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. =A0Column B contains the name of the contract. =A0Column C contains =
a
> flag to indicate the job has been completed. =A0Column 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.
> =A0 Contract #1 =A0 =A0$ 3,000
> =A0 Contract #2 =A0 =A0$ 2,500
> =A0 Contract #3 =A0 -$ 1,000
> =A0 =A0 =A0 =A0 Total: =A0 =A0 =A0 $ 4,500
>
> XYZ Inc.
> =A0 Contract #1 =A0 =A0$ 1,000
> =A0 Contract #2 =A0 =A0$ 6,500
> =A0 Contract #3 =A0 =A0$ 1,500
> =A0 =A0 =A0 =A0 Total: =A0 =A0 =A0 $ 9,000
>
> I am not familiar with macros and my understanding of formulas are at
> least at an intermediate level. =A0Any help would be appreciated.
> Thanks.

0
jbschipper (10)
11/21/2008 5:11:35 PM
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

"lukus2005@gmail.com" wrote:

> 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.
> 
0
11/21/2008 5:12:01 PM
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

On Nov 21, 10:12=A0pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> The easiest way is to use a Pivot Table. =A0Select the range of data and
> choose Data, PivotTable and PivotChart Report, click Next twice and on th=
e
> 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. =A0Click Next, choose New Worksheet a=
nd
> click finish.
>
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
>
>
> "lukus2...@gmail.com" wrote:
> > 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. =A0Column B contains the name of the contract. =A0Column C contain=
s a
> > flag to indicate the job has been completed. =A0Column 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.
> > =A0 Contract #1 =A0 =A0$ 3,000
> > =A0 Contract #2 =A0 =A0$ 2,500
> > =A0 Contract #3 =A0 -$ 1,000
> > =A0 =A0 =A0 =A0 Total: =A0 =A0 =A0 $ 4,500
>
> > XYZ Inc.
> > =A0 Contract #1 =A0 =A0$ 1,000
> > =A0 Contract #2 =A0 =A0$ 6,500
> > =A0 Contract #3 =A0 =A0$ 1,500
> > =A0 =A0 =A0 =A0 Total: =A0 =A0 =A0 $ 9,000
>
> > I am not familiar with macros and my understanding of formulas are at
> > least at an intermediate level. =A0Any help would be appreciated.
> > Thanks.- Hide quoted text -
>
> - Show quoted text -

0
anandydr (4)
11/22/2008 8:20:33 AM
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.



On Nov 22, 3:20=A0am, anandydr <anand...@gmail.com> wrote:
> 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
>
> On Nov 21, 10:12=A0pm, Shane Devenshire
>
> <ShaneDevensh...@discussions.microsoft.com> wrote:
> > Hi,
>
> > The easiest way is to use a Pivot Table. =A0Select the range of data an=
d
> > 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 th=
e Row
> > area and then the Contract button below it in the Row area, and then th=
e
> > Cost/Amount field to the Data area. =A0Click Next, choose New Worksheet=
 and
> > click finish.
>
> > If this helps, please click the Yes button
>
> > Cheers,
> > Shane Devenshire
>
> > "lukus2...@gmail.com" wrote:
> > > 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. =A0Column B contains the name of the contract. =A0Column C conta=
ins a
> > > flag to indicate the job has been completed. =A0Column 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.
> > > =A0 Contract #1 =A0 =A0$ 3,000
> > > =A0 Contract #2 =A0 =A0$ 2,500
> > > =A0 Contract #3 =A0 -$ 1,000
> > > =A0 =A0 =A0 =A0 Total: =A0 =A0 =A0 $ 4,500
>
> > > XYZ Inc.
> > > =A0 Contract #1 =A0 =A0$ 1,000
> > > =A0 Contract #2 =A0 =A0$ 6,500
> > > =A0 Contract #3 =A0 =A0$ 1,500
> > > =A0 =A0 =A0 =A0 Total: =A0 =A0 =A0 $ 9,000
>
> > > I am not familiar with macros and my understanding of formulas are at
> > > least at an intermediate level. =A0Any help would be appreciated.
> > > Thanks.- Hide quoted text -
>
> > - Show quoted text -

0
pleclerc (6)
11/23/2008 12:56:53 AM
On Nov 22, 3:20=A0am, anandydr <anand...@gmail.com> wrote:
> 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
>
>
> > - Show quoted text -

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.
0
lukus2005 (35)
11/24/2008 1:53:34 PM
On Nov 24, 8:53=A0am, "lukus2...@gmail.com" <lukus2...@gmail.com> wrote:
> On Nov 22, 3:20=A0am, anandydr <anand...@gmail.com> wrote:
>
> > 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
>
> > > - Show quoted text -
>
> 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.
0
lukus2005 (35)
11/24/2008 2:07:21 PM
Reply:

Similar Artilces:

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

Report.SalesPlusTax
I don't know if you guys have ever try to show the total tax sale on the z report, but when i make the modified on the z report it came out that the total tax is 0. It didn't show me anything, i don't know if the script is wrong or the script is not working for RMS V1.3?? Has anyone ever tried that?? <ROW> "Taxable Sales|" Report.SalesPlusTax </ROW> ...

Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...

crosstab qry as report source breaks Access2007
We have 3 reports whose record sources are crosstab queries. All have work fine on the pc's running Access2003. But, the one pc running Access2007 doesn't like the crosstab queries as the report source. We don't get an error - only the dreaded 'Access has encountered a problem and has closed...' It totally shuts down the access application. I stepped through the little bit of onOpen code while opening the reports and all works fine. I open the crosstab query and it opens just fine. To test the theory that it is a crosstab source problem, I tried to recreate one of the...

Report CRM 3.0
Hi, I would need to find out the detailed procedure (step by step) to customize a report…. Could anybody inform about any links or documents concerning this issue? Thank's Marco I'm not sure if it's detailed enough, but the technical training manual has a section on creating and customizing reports. You can find it here: http://www.microsoft.com/businesssolutions/crm/using/whatsnewtechnical.mspx HTH, -- Jeffry van de Vuurst CWR Mobility www.cwrmobility.com -- "Marco Rocca" <Marco Rocca@discussions.microsoft.com> wrote in message news:CEF80683-EC26-456C-82C...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Pin Generator
Anyone know how to make a simple PIN generator in excel? -- Jeff =RAND()*(9999-1) Try typing this into a cell and change its number properties to having no decimal places. "GoBucks" wrote: > Anyone know how to make a simple PIN generator in excel? > -- > Jeff Jeff, How about entering this formula in a cell, thenRecalcing to generate a new number each time? =INT(RAND()*10000) Regards Pete "GoBucks" wrote: > Anyone know how to make a simple PIN generator in excel? > -- > Jeff ...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

stock variance report
Hi there, Is there any way to edit that stock variance report at HQ? There are no $$ values and this is quite important at HQ. How do others work with this? IB Ivan, Create a custom report using the Quantity Discrepancy List as a template, add the SnapShotCost, SnapShotLastCost and/or SnapShotReplacementCost column from the ItemDynamic table times the qty difference. -- * "Ivan Brebner" <ibrebner@premiumretail.com.au> wrote in message news:%23Idw6wPAGHA.2912@tk2msftngp13.phx.gbl... Hi there, Is there any way to edit that stock variance report at HQ? There are no ...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

Report Filter focus
Hey all, I am experiencing an issue with the report filters in RMS. It is not a show stopper, but annoying. When I launch a report in SO Manager on a win XP machine, RMS version 1.3, the report filter is there, but the correct field is not selected and the filter under Filters is not selected. When this happens and I want to change the filter, I first need to click on the Filter itself in the bottom box and then select the correct field out of the Fields list, then change the Value. Is there anything I can do to fix this? To use a real report as an example, When I run the Detailed Sales rep...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

Outlook reports OLE registration error
I have Office 2000 running in Windows Me on a Dell Dimension XPS D266 with 64 meg ram. Outlook stopped working so I ran Repair from Office 2000 CD and then Re- install Office from same source. Both times Outlook says" An OLE registration error occured. Program is not correctly installed. Run Setup again." Doesn't seem to be much sense in running it again. What now? Reinstall Outlook Express. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal acco...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...

Help
Hi... I tried to work on Word today for first time on newish Mac and realized none of my Office applications work. When I double click on Word I can see something happening as I get the circle of colours twirling, but half an hour later it still hadn't opened. I just have to do this as quickly as possible as I have a deadline for what I have to do Monday at 12. I've tried reinstalling the software, but that didn't do anything for me. Mind you I know absolutely nothing about installing anything on Mac computers so I could be doing it all wrong. Do any of you have any idea what c...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Help!
We went live with 4 decimals on our functional currency and we now want to reduce them to 2 decimals. All of our transactions so far are in 4 decimals. Can anyone guide me into doing this? thank you Use the Change Decimal Places window to change the number of decimal places used for keeping quantity or currency amounts for items. You might need to complete this procedure if an item was set up improperly, or if you’re changing the units of measurement for an item. NOTE: If an item is on an unposted transaction, you can’t change its decimal place settings. Ensure you have a complete ba...

Need to show file properties dialog box
Hello I need to show the shell's file properties dialog box. I tried converting old vb6 code into vb.net but it gives me error. Here's my code: Private Structure SHELLEXECUTEINFO Dim cbSize As Long Dim fMask As Long Dim hwnd As Long Dim lpVerb As String Dim lpFile As String Dim lpParameters As String Dim lpDirectory As String Dim nShow As Long Dim hInstApp As Long Dim lpIDList As Long Dim lpClass As String Dim hkeyClass As Long Dim dwHotKey As Long D...

Exchange server reporting tool
Could someone recommend an exchange server reporting tool other than promodag? I will really appreciate the help. Thanks sl =?Utf-8?B?c2xhd2Fs?= <slawal@discussions.microsoft.com> wrote in news:735D2C55-FB0E-44F4-8F6B-274B754A9FBE@microsoft.com: > Could someone recommend an exchange server reporting tool other than > promodag? I will really appreciate the help. Quest messagestats is good stuff. Regards, -- Arlo Clizer FAQ: http://www.exchangefaq.org Archives: http://groups.google.com i second that... -- Susan Conkey [MVP] "Arlo Clizer" <aclizer@online.goo...

Object error when trying to print custom reports
I am getting an [object error] message when trying to print custom reports or modified versions of canned reports. They display just fine within CRM, we just can't print them. I have already tried deleting the ActiveX to force a reload and manually reinstalling it using http://crmserver/viewer/activexviewer/en/npviewer.exe with no luck. This is occuring on systems regardless of whether Crystal Designer is loaded. Any ideas? Open the reports in Crystal that you are having problems with and make sure that the "Save data with reports" option is not checked. (In Crystal...

Please help a exchange newbie.. Message filtering
I have a contact set up on Exchange 2003 that forwards to a cell phone for alerts generated in our environment. This contact is setup to get notice of several events, all events have separate email address as the "sender" one of the events gets allot of notices. I would like to have this contact not receive mail from the one email address that generated allot of events. I have set up the properties under the exchange general > messaging restrictions, and have authenticated users only and from everyone except the email account I don't want to receive alerts from, BUT when anyon...