2 Questions on Pivot Tables - 1 tip

Any assistance appriciated. (Excel 2003)

1)    I have a simple Pivot table showing 6 groups with their totals and a 
subtotal eg:
        America    10
        UK           15
        Oz             22  etc.
    Grand Total  125

I want to Pivot Graph this data, no problems.   I want however an extra line 
to show the total on the Secondary Axis.  I have almost got it by placing 
the column in a second time as a Running Total and then as a Line on my 
chart, hiding all the values until the last point.

However I would like if possible the Total as a horizontal line across the 
chart?

2)  I have come across Grouping Dates and the inability to Group if there 
are blanks.  I thaught about generating an invalid date eg 01/01/1900, to 
use that and ignore it later, I thaught about sorting and inserting a row to 
knock the blanks out of my pivot using a macro.....

Then a thaught, I tried linking the data into Access 2003 and writing a 
Pivot Chart query and it worked.  I am VERY happy to discover this, only a 
simple questions, was it possible in Excel....?

Roger Knowles
MOS Master all Versions
England. 


0
2/25/2005 10:14:34 PM
excel 39879 articles. 2 followers. Follow

0 Replies
615 Views

Similar Articles

[PageSpeed] 55

Reply:

Similar Artilces:

Business Portal 2.0 Installation on GP 7.5
Hello Everyone, Has anybody successfully implemented Business Portal in their systems? I have set up Business Portal on a notebook which acts as the application server, web server as well as terminal server for demo purposes. I have tried to access the Great Plains windows from the tasks hyperlink in each center but I failed. I get error messages. I am also not able to view published FRx reports from the portal. It seems that I need to know SharePoint to modify the pages & web parts but I am unable to find any relevant helpful documentation. Can anybody help me? Regards, Saras ...

Where is Excel.h? #2
I am having a problem finding Excel.h. I understand the file is created when you follow the instructions in the knowledgebase article. I am running Visual Studio 2008. Mot MFC 6.0 So, I cannot generate the files. Do I need to install C++6.0, just to get the files Excel.h and Excel.cpp? Any advice/ suggestions would be appreciated. I see the same question was asked a couple of years ago, and I see that the individual found a solution, but he does not actually say that the solution was to generate the files under C++ 6.0. Thank you. Which knowledgebase article? AliR. "Cameron_C&qu...

File Not Found #2
I have a spreadsheet that is linked to over 100 other spreadsheets. Currently, a number of the linked spreadsheets are not saved on my computer. Every time I open the main spreadsheet, I get a hundred popups that say "cannot find file xxx.xls". Is there a way to open up the main file and avoid all these popups. in the workbook_open put in on error resume next i think that will work. >-----Original Message----- >I have a spreadsheet that is linked to over 100 other >spreadsheets. Currently, a number of the linked >spreadsheets are not saved on my computer. ...

Array Problem #2
Hi, I can't get my array formula to work properly. I see this problem in both Excel 2003 and 2007. The data fields are as follows: A1: 1 A2: 2000 B1: GB B2: MB The formula is: {=SUM(IF(B1:B2="MB",A1:A2/1000,A1:A2))} The result is: 3 (as it should be) But if I should change the values in cell A1:A2 from numeric's to text, the result should change to zero as text can not be added. But this is not what happens. Instead, cell A1 is not added (as it should not be), but cell A2 is somehow treated as a numeric and is added to the result! Example: A1: '1 A2: '...

Language #2
Please tell me how to send email in Japanese and Korean. I am using outlook express. Thank you young zeon wrote: > Please tell me how to send email in Japanese and Korean. > I am using outlook express. Thank you This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Intel PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x microsoft.public.windows.inetexplorer.ie55.outlookexpress for OE 5...

queries #2
I have a co-worker who wants to be able to do a query within a worksheet - it should be ridiculosly easy, but I can't seem to figure it out, and Excel 2003's help only speaks of importing queried data into Excel, and that's not what I want. help?? "Dave" <Dave@discussions.microsoft.com> wrote ... > I have a co-worker who wants to be able to do a query within a worksheet - it > should be ridiculosly easy, but I can't seem to figure it out, and Excel > 2003's help only speaks of importing queried data into Excel, and that's not > what...

Site #2
Hi, Can You tell me What is A Site ? Have a look at this article: http://www.winnetmag.com/MicrosoftExchangeOutlook/Article/ArticleID/8743/MicrosoftExchangeOutlook_8743.html Glen "niru" <niru@discussions.microsoft.com> wrote in message news:4788B71F-430F-49A8-9164-7023E234F287@microsoft.com... > Hi, > > Can You tell me What is A Site ? ...

Showing field after page 1
Hello, I have an Access 2003 report that show several fields on the top of the first page (one of thses fields is the WorkOrderID), these fields are in a "WorkOrderID" header. The work order details can span several pages, on the 2nd, 3rd, etc pages I just want to show the WorkOrderID and not the other fields that show on the first page. Can I somehow put the WorkOrderID field in the PageHeader but tell it not to show on the first page? Thanks On Wed, 12 May 2010 14:41:00 -0700, Don wrote: > Hello, I have an Access 2003 report that show several fields on the to...

CRM Sales for Outlook 1.2
Hi! We are using mscrm 1.2 successfully for a while now (no mail router, no outlook client). Now we tried to integrate it into our exchange environment using the outlook client. Online is working fine but the offline functionality isn't working. Obviously the local msde- database is not getting any data. The error message is: "Metadata-Cache error, No entity!" The log says: MSCRM Platform Error Report: ---------------------------------------------------------- ---------------------------------------------- Error: Exception from HRESULT: 0x80040E00. Error Message: Exception...

Service STORE is falling #2
Hello to All, I need a help with the problem that is happening with our server of exchange 5.5 and windows NT 4.0. The problem that is happening is that suddenly the service of STORE falls, seeking the mistake that is happening found the following note of MS that I am enclosing, only that we already sought and we didn't get to find a program to be accessing some of the mentioned doors. Did this problem happen please already with you or you know what can be? Regards, Lincoln Rollin Backup and apply SP4 and post-SP hot-fixes. Lincoln Rollin wrote: > Hell...

Pivot Table percentages #2
I have created a pivot table that is running off some data that looks like this (simplified version): Name Accept Decline Joe 1 0 Mary 1 0 Fred 0 1 The pivot table then counts these columns to give the total number of accepts and declines (on 2 seperate lines). Is there a way within the pivot table to represent the declines as a percentage of the accepts? I've tried using the % of Row and % of Column, but I can't find a way of doing it as a percentage of a different column/row. I have...

If Then Statements #2
Need help. Need to turn the following into a formula within Excel. If B19>45 then A19 If B19>=45 but <100 then B19*A20 If B19>=100 but <300 then B19*A21 Thanks, Hi try =IF(B19>=100,IF(B19<300,B19*A21,"not defined"),IF(B19>=45,B19*A20,A19)) I assumed that your first condition should read B19<45 -- Regards Frank Kabel Frankfurt, Germany mh4568 wrote: > Need help. Need to turn the following into a formula within Excel. > > If B19>45 then A19 > If B19>=45 but <100 then B19*A20 > If B19>=100 but <300 then B19*A21 > >...

Delete Pivot Table Formula
How do i delete calculated fields from pivot table field lists? Thanks, sduffield Please check your previous post of this question... -- HTH... Jim Thomlinson "sduffield" wrote: > How do i delete calculated fields from pivot table field lists? > > Thanks, > sduffield ...

Font size question
sorry, this question is not Windows XP related, but about POS printer: when in specification noted, that POS printer has two fonts: Font A is 12x24 dots, Font B is 9x17 dots (Dot = 0.123 mm): does this mean font width x height, or this mean height of lowercase and uppercase symbols? (12 and 24) thanks. On 1/19/2010 8:03 AM, amber wrote: > sorry, this question is not Windows XP related, but about POS printer: > when in specification noted, that POS printer has two fonts: Font A is > 12x24 dots, Font B is 9x17 dots (Dot = 0.123 mm): does this mean font > width x h...

Unable to Edit Lookup Tables (Error: 'ExcelBtn' is undefined)
Project Server 2007 SP2 A week or so ago, we were happily adding/modifying Lookup Tables...but today when I click on an existing lookup table to modify (or select the 'New Lookup Table' button) I received a mostly blank screen. Strange thing is, when I'm trying to edit an existing lookup table the resulting screen says "New Lookup Table", some empty frames are barely visible. Clicking on the details of the accompanying error message: Line: 1366 Char: 8 Error: 'ExcelBtn' is undefined Code: 0 I can use the copy lookup table feature successful...

2 copies of every new e-mail!
I asked my computer guru to make sure my e-mail goes to my desktop PC and my laptop. That seems to be working, but now I am getting 2 copies of every e-mail in my inbox Can somone tell me how to turn this off so I only receive one copy of each e-mail? On 30 Apr 2004 10:06, "Mark" <anonymous@discussions.microsoft.com> wrote: >I asked my computer guru to make sure my e-mail goes to my desktop PC >and my laptop. That seems to be working, but now I am getting 2 copies >of every e-mail in my inbox. > >Can somone tell me how to turn this off so I only receive one...

vba copyright protection #2
I have created a spreadsheet which utilizes many of my own custom built macros and formulas. Does anyone know whether or not my vba code and/or formulas can be copyrighted--ie legally protected? thanks, Bill --- Message posted from http://www.ExcelForum.com/ ...

Moving a line chart data point revises data table value in Excel '
I recall a capability I used with Excel years ago that enabled me to create a line chart from a data table, then move the plot points on the chart to "smooth" the line, and the source data chart value would change with the corresponding new plotted point. I'd like to use that capability with Excel 2007 but can't find to activate it. Any power users know how? Thanks! Ed Ed, Microsoft decided that users didn't need that feature. More than likely, they were tired of support calls asking why the values changed when one dragged a data point...... No really, they took i...

Cannot copy table from Excel 2007 to Word 2007 w invisible grid li
How do I copy a table from Excel 2007 to Word 2007 with invisible grid lines? In previous version of Word, it was possible to do so by clicking on "insert special" and choose one of the categories (I have forgotten which). With Word 2007, the grid lines become visible no matter which option I choose under "insert special". Is it possible for you to help me? Thanks! To hide the gridlines, click the View Gridlines button (to deselect the option) on the Table Tools Layout tab. This setting will be preserved between Word sessions. If you are in fact referr...

Basic Acct Question Checks and Witholdings
Hello all. Ok basic accounting 101 probably. What destination would a check and witholdings be applied against in Great Plains? Would that be accounts payable as a payables transaction? We have quarterly check runs that happen outside of great plains and an outside vendor cuts checks. We want to start logging these checks and witholdings possibly on those checks in GP so we can 1099 report at the end of the year. I am just trying to figure out where to start. I will want to use Integration Manager to handle this once I get all the fields together such as Customer ID, Check Numbe...

Outlook Client Error #2
I have one user that is receiving the following error after installing the CRM 4.0 Outlook client. "Your server administrator has limited the number of items you can open simultaneously. Try closing messages or removing attachments and images from unsent messages you are composing." This happens after using Outlook for a few minutes. If I remove the client the error will stop appearing. She is using Outlook 2007 on windows XP. I have applied Hotfix kb970944 and all applicable updates to Outlook. The user dose have a large amount of emails in her inbox, but I have other u...

How do I change the Product Key code and the User name of Microsoft Office 2008 once installed on Mac OSX 10.5.1?
How do I change the Product Key code and the User name of Microsoft Office 2008 once installed on Mac OSX 10.5.1? On 2/26/08 5:15 PM, in article ee8ee56.-1@webcrossing.caR9absDaxw, "mollycat@officeformac.com" <mollycat@officeformac.com> wrote: > How do I change the Product Key code and the User name of Microsoft Office > 2008 once installed on Mac OSX 10.5.1? Delete these two files: ~:Library:Preferences:Microsoft:Office 2008:Microsoft Office 2008 Settings.plist where ~ is your home folder, and HD:Applications:Microsoft Office 2008:Office:OfficePID.plist -- Diane, ...

Content select question?
Dear All, I wonder if any property or method could to check the current document content is selected all or not? Many thanks David If you mean all as in the context of using CTRL+A then If Selection.Range.Start = ActiveDocument.Range.Start And _ Selection.Range.End = ActiveDocument.Range.End Then MsgBox "The document is selected" Else MsgBox "Only part of the document is selected" End If would do that, but it would be better to assign a range rather than a selection and then you would not need to make the test. Set oRng = ActiveDoc...

external data not in a table
I have a worksheet originally created in excel2003 that gets external data from a db2 database. After migrating to excel2007 the external data in the worksheet is not in a table (clicking on a result cell does not make Table Tools appear). When creating a new database query I find that the results always get put in table. I don't want the results in a table. How do I make the results of a new query NOT be a table? And I don't want to break the link to the database since I update the results periodically. ...

Exchange tabs missing on 2 other 2003 DC's
I have a simple question (I hope) I used to have a 2000 GC with exchange 2000 on it. I have now moved to a 2003 domain and have migrated to Exchange 2003. This is all on 1 server, and I know "now" that having the GC on the Exchange server is not a good idea (but we all make due on a limited budget). I have just recently added 2 DC in a hope to move the GC off of the exchange server. Here is my question: Why are the Exchange tabs missing from the 2 other DC’s? I was under the impression that I did not have to install the Exchange management tools on the other DC’s. If this...