 |
|
|
|
Next: EXCEL 2003 paste special and go to specil misfunc..
|
| Author |
Message |
External
Since: Oct 23, 2008
Posts: 3
|
(Msg. 1) Posted: Fri Nov 21, 2008 8:58 am
Post subject: Need help in generating a report.
Archived from groups: microsoft>public>excel (more info?)
|
|
|
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. |
|
| Back to top |
|
 | |
External
Since: Nov 11, 2008
Posts: 1
|
(Msg. 2) Posted: Fri Nov 21, 2008 9:11 am
Post subject: Re: Need help in generating a report.
Archived from groups: per prev. post (more info?)
|
|
|
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 am, " "
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. |
|
| Back to top |
|
| |
External
Since: Nov 13, 2008
Posts: 17
|
(Msg. 3) Posted: Fri Nov 21, 2008 9:12 am
Post subject: RE: Need help in generating a report.
Archived from groups: per prev. post (more info?)
|
|
|
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
" " 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.
> >> Stay informed about: Need help in generating a report. |
|
| Back to top |
|
| |
External
Since: Nov 22, 2008
Posts: 1
|
(Msg. 4) Posted: Sat Nov 22, 2008 12:20 am
Post subject: Re: Need help in generating a report.
Archived from groups: per prev. post (more info?)
|
|
|
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 pm, Shane Devenshire
wrote:
> 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
>
>
>
> " " 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.- Hide quoted text -
>
> - Show quoted text - >> Stay informed about: Need help in generating a report. |
|
| Back to top |
|
| |
External
Since: Nov 22, 2008
Posts: 1
|
(Msg. 5) Posted: Sat Nov 22, 2008 4:56 pm
Post subject: Re: Need help in generating a report.
Archived from groups: per prev. post (more info?)
|
|
|
HI
Discover the most powerful function in Excel: SUMPRODUCT:
[URL="http://excel-examples.com/excel-3-7-formula-sumproduct.htm"]http://excel-examples.com/excel-3-7-formula-sumproduct.htm[/URL]
If 5% of Excel users knew about SUMPRODUCT the face of business
reporting would change.
On Nov 22, 3:20 am, anandydr 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 pm, Shane Devenshire
>
> wrote:
> > 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
>
> > " " 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.- Hide quoted text -
>
> > - Show quoted text - >> Stay informed about: Need help in generating a report. |
|
| Back to top |
|
| |
External
Since: Oct 23, 2008
Posts: 3
|
(Msg. 6) Posted: Mon Nov 24, 2008 5:53 am
Post subject: Re: Need help in generating a report.
Archived from groups: per prev. post (more info?)
|
|
|
On Nov 22, 3:20 am, anandydr 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. >> Stay informed about: Need help in generating a report. |
|
| Back to top |
|
| |
External
Since: Oct 23, 2008
Posts: 3
|
(Msg. 7) Posted: Mon Nov 24, 2008 6:07 am
Post subject: Re: Need help in generating a report.
Archived from groups: per prev. post (more info?)
|
|
|
On Nov 24, 8:53 am, " " wrote:
> On Nov 22, 3:20 am, anandydr 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. >> Stay informed about: Need help in generating a report. |
|
| Back to top |
|
| |
| Related Topics: | calculate percent of a col in pivot report - Hi, I have a pivot report based on a query against a database. The query has 3 columns Gender, CntMale, CntFemale In the pivot report, I need to show the percent of Male, for which I believe I need to have the column display the value in the CntMale....
INDEX MATCH HELL ooops.. I mean HELP! - I know (or at least I think I do) that I can do this with INDEX MATCH. Here is what I want to do. I want to place this formula in cell C4 of Sheet 1. What I want this formula to do is to match the value of B2 of Sheet 1 with a list that is in Sheet 2...
Notation Bugs - Did anybody notice that Excel handles math notations (symbols) in an incorrect way? Try this: write -2 in cell A1. Write =1-A1^2 in a different cell. Write =-A1^2+1 in another cell. The two expressions are equivalent, shouldn't they? Well, the former..
How to create a single line separated by commas from a mat.. - Hello, I've the following problem. I've a table made of several columns. Each colum represents a characteristic regarding to a subject (e.g. Name, Surname, Home Street, etc...). In the rows I put the different people. I need to export these info to a....
Excel password Recovery - Hi All, i forgot my excel password, can any tell me how to recover the password there is any free tool is avail in web, Regards, Deen |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
