Pivot Table question:

If I have a simple spreadsheet, with:

      acctNo Call Month Number of Calls
      23142 Feb-2003 12
      23142 Mar-2003 90
      25943 Feb-2003 24
      25943 Mar-2003 32
      26044 Mar-2003 1
      1260 Feb-2003 144
      1260 Mar-2003 615
      25828 Mar-2003 59
      17688 Mar-2003 359
      5235 Mar-2003 4
      23042 Mar-2003 53


Then I can very easy generate a pivot table like this:

      Sum of Number of Calls Call Month
      acctNo 2003/02/01 2003/03/01 (blank) Grand Total
      1260 144 615   759
      5235   4  4
      17688   359  359
      23042   53  53
      23142 12 90  102
      25828   59  59
      25943 24 32  56
      26044   1  1
      (blank)
      Grand Total 180 1213   1393



BUT, if my spreadsheet looks like this:

      acctName acctNo Call Month Amount Number of Calls
      AC Malan t/a Ari Malan for Plot 23142 Feb-2003 R 31.46 12
      AC Malan t/a Ari Malan for Plot 23142 Mar-2003 R 216.06 90
      Academy for Mathematics and Science (Pty) Ltd. 25943 Feb-2003 R 59.65
24
      Academy for Mathematics and Science (Pty) Ltd. 25943 Mar-2003 R 71.46
32
      Aero Clothing Manufactureres (Pty) Ltd 26044 Mar-2003 R 7.17 1
      Africon Engineering 1260 Feb-2003 R 405.25 144
      Africon Engineering 1260 Mar-2003 R 1,524.14 615
      Africon Engineering International t/a Africon Engineering
International 25828 Mar-2003 R 187.72 59
      Africure Management Services (Pty) Ltd 17688 Mar-2003 R 898.22 359
      Afrisun Gauteng 5235 Mar-2003 R 8.14 4


HOW can I easy get my pivot table that:

For each AcctNo, also have the AcctName next to it.
For each Account, and for each Call Month, have BOTH the Sum of Number of
Calls and Sum of Amount next to each other.


Regards
Louis


0
Louis
7/24/2003 9:46:00 AM
excel 39879 articles. 2 followers. Follow

0 Replies
618 Views

Similar Articles

[PageSpeed] 57

Reply:

Similar Artilces:

Pivot Table questions #2
I have several questions about a pivot table I am constructing. 1. Is there a way to have a column reference a cell with data instead of using the drop downs? example - first column in table is part number, instead of using the drop down to search for parts is it possible to reference cell A1 where a part number would be typed by the user. 2. Is there a way to lock a table so the columns can not be added or removed? 3. I do not have anything in the data items or column fields sections, is there a way to remove these sections? Thanks ...

Workflow question 03-16-06
There is a field on the Administraton tab of the Account form called Prefered User. Is it possible to use that field in a Case wrokflow. I am trying to use a workflow which will determine who the this field in an IF statement but it does not seem to be available to me. ...

Range problem in Pivot table
This is the problem I have with few excel sheets but not with all. When I select a range of cells including the Column headers and go for Data - > Pivot table and Pivot Chart report ... and click on Next , I can see "Database" in the Range field rather than the range (example : $A$1:$B$7). Could someone help. Hi Ravi, You have not mentioned which version of excel you are using. One possiblity might be that the range you are selecting, already has the name defined as "Database". Sometimes excel, itself defines names such as "data" or "database&quo...

Pivot Table Custom Calculations #3
I have a pivot table with the "columns" set up as Budget - Actual - Variance for each month of the year. The rows are the expenses account, such as supplies, maintenance, etc. I want to be able to add the total of the "Actual" spent through September plus the "Budget" for October through December, basically to get a projected total by expense for the year. How can I do this? Thanks for your help! You could add a column to the source data, and calculate which amount should be used. Then add that field to the pivot table. For example, with dates in colum...

Pivot Table Auto Update?
I'm not too familiar with VBA, so I was wondering if there's a way tha I can have a spreadsheet automatically update itself from a database. Basically, I have a database in Access, and I would like to work wit the data in Excel (as a pivot table). I would like to find a way t have Excel automatically update the pivot table when the spreadshee starts up. Is that do-able? Any examples? We'll assume that the database is data.mdb and is in th same directory as the spreadsheet. Thanks! mat -- mkaak ----------------------------------------------------------------------- mkaake'...

Newbie Question
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01C3BB50.487A0580 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Exchange 2000 Native mode. Can I Re-name my first administrative group on the fly? will doing so = break anything? Anyone out there have any bad experiences in renaming = the first and only, or subsequent administrative groups - any = information would be greatly appreciated. Rummie ------=_NextPart_000_0029_01C3BB50.487A0580 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Enco...

Security setup question
Hello everybody, We have Dynamics GP 9.0, Active Directory 2003, and SQL 2000. Is there a document that can tell me what is the best way to secure Dynamics GP. We want to ensure that certain users have only certain permissions to certain modules/windows etc. We are looking for a document that talks about security at the granular level. Michael Hi Michael Have a look at this whitepaper. https://mbs.microsoft.com/customersource/documentation/whitepapers/securityplanning_eng.htm?printpage=false Without SQL 2005, you will not be able to get password expiry and policy functionality unles...

How I can switch row/column in pivot chart?
Hi, I have the new office 2007, in excel I try to draw pivot chart, I succeded. But I want to switch row into column and column into row. When I try to do that tis button bocomes invisible while I can do in PPT. Please help me!! Regards, Tuba Kesen Umar Pivot charts in Excel 2000 through 2007 only allow you to plot series in columns. However, you could rebuild the pivot table so that your old column fields are located in the rows area and vice versa. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "kesen...

Storage Limits delegation question
I am trying to create a delegation for a user account manager group so it can change the storage limits on a user account. This is a brand new forest and exchange 2003 setup and all the users are new accounts. I have added the delegations listed below and I can successfully update the fields only after an admin does it first. Is there a way to grant my user account manager group the ability to do this without intervention from the admins? When someone in the user account mangers group tries to set any of the values I get an Access Denied LDAP Provider id 80070005 Microsoft Active Di...

HELP!!!! PIVOT TABLES
I am 17, and have just accepted a new job to start in 2 weeks, only problem is, it involvs using pivot tables. Please don't think I'm stupid, but have never used these before and am not even sure what they are, let alone how to do them!!!!!!! Any help would be greatly apprieciated!! To summarize the data in a table, you can use a Pivot Table. There are instructions in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm Dawn (17) wrote: > I am 17, and have just accepted a new job to start in 2 weeks, only prob...

Dumb MFC Question
I have a really dumb question. In my code when I create a CPen, CFont, CBrush, ... and select them into a device context (CDC or CClientDC or other MFC wrapped DC). These objects are not cleaned up. Johan Rosengren has been helping me with some issues (Thanks Johan for you time away from your family this weekend) and he said that I needed to delete the CFont and other objects. That got me curious and I looked at the destructors of these objects (that can be selected into the DC) and sure enough, they don't call DeleteObject method to release them from the system. These include: CPen C...

Searching for first and last in a table to chart (gantt)
I am using a rather large table and need to search for the first and last cell that has a value entered in a particular row and return the cooresponding date in that column. The table has a few columns showing task name etc that there is serveral rows that represent a date. In each row a number is put in a column that matches a date or is left blank. I have tried to llustrate this below __ |A1__|___B1___ |__C1_|__D1_|....|__E1_|__H1_|__I1_|__J1_|__K1__| A2|Task | Sub Task |Phase| Name| |01/05| 02/05| 03/05|04/05| 05/05 | A3| Xt Xs Yp Xn | null | ...

VB question
I would like some advice please. I am trying to learn VB6. I was recommended a very expensive book, Programming in Visual Basic 6.0. Before I decide to keep this book. I would like to know if the practice/examples in this VB book can be used in Microsoft Visual Basic Access 2003? Thank you for any advice. Sorry, I don't have a copy of the book, so I can't give a definitive answer. However, the language components of VB and VBA are very similar. The major problem you may run into is the fact that the controls available for forms in VB are quite different that for forms in Ac...

Table font size changes
If I try to make the font size in a table smaller so I can fit more on a slide, after I save and close the document, when I reopen it, the font size in the table has gone to a larger size. In article <4451947A-8D54-46A8-A870-C6BD6BCD8245@microsoft.com>, JayR wrote: > If I try to make the font size in a table smaller so I can fit more on a > slide, after I save and close the document, when I reopen it, the font size > in the table has gone to a larger size. This doesn't ring any bells with me, but in case it does with someone else, start by telling us which ve...

Selecting dynamic number of columns into fixed-structure table
I have a table with the following columns (the Pen# columns are all REAL data types and all columns support NULL values): dtDateTime Pen1 Pen2 Pen3 Pen4 Pen5 Pen6 Pen7 Pen8 Pen9 Pen10 I have a stored procedure that returns data similar to the following: dtDateTime TT_S02_20 TT_S02_30 TT_S02_50 ----------------------- ------------- ------------- ------------- 2010-06-26 00:00:06.653 148.1323 115.5447 124.679 2010-06-26 00:00:21.687 148.5214 115.5...

If Vlookup Question
Hi, I have created the following equation using VLOOKUP... =VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE) The result varies depends on my data, but I would like any result that is $0 to equal "***" Does anyone have any ideas? Thanks so much for your help!! Hi Brent, If you are only wanting results that are under a certain amount you can use: =if(VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)>A1,0,VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)) I placed the "upper limit" in cell A1. If you are wantin...

HQ SO Question about costs for transfers
I have a location in Country A using store ops and am opening another location in Country B. I will be getting HQ soon and store ops for Country B. All items will be received in Country A, so there will be a cost associated with the item. However, when I transfer goods to Country B to put into thier stock, I will need to change the cost to account for duty and freight. In other words, the transfer in for Country B will need to have a different cost from the transfer out from Country A. Does HQ and/or SO have the capability to do this? Jason P.S. See also my thread about adding freigh...

Ranking question
Hello - Each individual has many records that are ranked as follows: Jim 1 Jim 2 Jim 3 George 1 George 2 Bob 1 Bob 2 Bob 3 Bob 4 I need a list returned with each individual's highest ranking e.g. Jim 3 George 2 Bob 4 How do I get to this result? -- Sheldon Select FirstName, Rnk = Max([Rank]) From YourTable group by FirstName "Sheldon" <Sheldon@discussions.microsoft.com> wrote in message news:4CF4060C-8E9B-40FF-...

Importing data question
I have a text document that contains unique job numbers, location of th job, date and time last scanned, number of breakages, days in process customer number etc. Several of the fields change multiple times durin a day but job number is unique and would dissappear only once a job i completed. I can figure out how to get external data into an exce spreadsheet and how to refresh the data but my problem is I want to b able to add comments/notes to each one (customer was called regardin delay/part on back order/called customer on xx/xx/xx etc.) Each time refresh data my notes are in the row but ...

pivot table percentages
Using XP and Office 2003 I created a pivot table to analyze a group of email address extensions. (".com" or ".org" or ".gov", etc.) The pivot table returned the count of each extension type and the grand total. Now I want to create an additional column that will calculate the percentage of the total for each extension type. I built the first formula at the end of the first row of data in my pivot table. The problem I am having comes when I try to copy that formula down or even when I try to copy and paste special that formula. I do not understand the w...

Access Switchboard Question
I am trying to open a file in another application (MS Word) from my switchboard in access. I am using Office 2003. How can I do this? Open the switch board in design mode and display the CONTROL TOOLBOX. Verify that the WIZARD button is activated (magic wand trailing fairy dust button) Click the command button tool. In the CATEGORIES column select APPLICATION and in the ACTIONS column select RUN MS WORD. Click NEXT Select the PICTURE you want to use or the text prompt you want to use and click NEXT. Give the command button a name (optional) and click FINISH. Save the form and give ...

eConnect and Trade Discount question
I'm currently using eConnect to insert SOP Transactions. All is going well except for the insertion of Trade Discounts. When I send the orders to GreatPlains I do not know the actual pricing so I have used the field "DEFPRICING" (default Pricing) and set it to 1 or to default the price in. My problem is I know the Trade Discount percent for each order but I do not know the actual amount since I do not know the actual numbers till tne transaction is over. Can anyone tell me if there is a way to flag that the Trade Discount is a percent and not an amount? Also I have pla...

Programming Question: Multi-Player Poker Game
I have a programming task and was wondering if anyone has some thoughts on the best way to achieve this. I am programming a mulit-user poker game using ASP.Net 3.5 and AJAX. I do not want to write a Java applet or any type of standalone program that would reside on the user's computer. I just want the user to use a browser as the user interface. I have 5 users at the table. Each user has their own browser session (i.e. they are located on different computers around the world). Player 1 is the first player to act. He bets $5. My question: How can the other users be n...

Virtual pc 2004 question
I am installing the microsoft virtual pc 2004 and the service pack 1 for the first time and I am getting the following message: Virtual PC could not open the Virtual Machine Network Services driver. Access to the external network and host will be unavailable to all virtual machines using virtual networking. Virtual machines using virtual networking will still be able to access other virtual machines using virtual networking. To fix this problem, re-enable the Virtual Machine Network Services driver on one or more ethernet adapters or reinstall Virtual PC. I do not know what the Virtua...

question #2
Version: 2008 HI friends I wanna ask that How corruption can be killed? ================================ sameer =========================== <a href="http://www.hotmail247.com <http://www.hotmail247.com>">Hotmail Com</a>-Hotmail Com Hi buddy I do agree with you that MC Baseball team has a crack at no.1 Ialso think the same.I am very happy to get that comment from you .so I am thanking you for giving this wonderful suggestions. ================================= sameer =========================== [url=http://www.hotmail247.com <http://www.hotmail247.com]Hotma...