problem with pivot table

Hello all:

I am trying to make a pivot table from my date.

My data includes Names, Dates (which I formatted to month, "m" in the
format cells option)and Amount due.

In designing the pt, I have "Name" in the row field, "Date" in the
columnn field and "Amount due" in the Data field.  

I want my data to be grouped as follows:
Month
1        2        3      4
Name     $100      $20      $30    $20

Instead I am getting more than 1 month column i.e.

Month
1        1        2      2       2       3  
Name     $80      $20      $30    $20


Do you know why this is happening?  How can change this?

Thanks a lot for your help.


---
Message posted from http://www.ExcelForum.com/

0
5/11/2004 6:03:32 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
451 Views

Similar Articles

[PageSpeed] 15

Even though you formatted the dates as "m", the pivot table is still 
seeing them as individual dates. Click on one of the heading cells, and 
in the formula bar you'll see the full date. So, each date gets its own 
heading.

You could group the dates by month, and you'll see month names in the 
headings. Or, add a column to the source data table, and calculate the 
month number, e.g. =MONTH(A2)
Then, add that field to the column area of the pivot table.

RichardO < wrote:
> Hello all:
> 
> I am trying to make a pivot table from my date.
> 
> My data includes Names, Dates (which I formatted to month, "m" in the
> format cells option)and Amount due.
> 
> In designing the pt, I have "Name" in the row field, "Date" in the
> columnn field and "Amount due" in the Data field.  
> 
> I want my data to be grouped as follows:
> Month
> 1        2        3      4
> Name     $100      $20      $30    $20
> 
> Instead I am getting more than 1 month column i.e.
> 
> Month
> 1        1        2      2       2       3  
> Name     $80      $20      $30    $20
> 
> 
> Do you know why this is happening?  How can change this?
> 
> Thanks a lot for your help.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/12/2004 2:29:48 AM
I have the same problem. Just because you have formatted the date t
look right does not mean that Excel will treat it as the month you hav
displayed. I think we both need the same solution - I want Excel t
store the displayed date of Jan 04, as the same and not a date value s
that when i use pivot table I get all the entries for a month as on
line , not multiple lines.
I agree with Debra bt in my case I want the month and year, like Jan 0
and not '1'

Who can help, please ?
;

--
Message posted from http://www.ExcelForum.com

0
5/12/2004 3:57:27 AM
Add a column to the source table, and convert each date to the first of 
its month. For example:
   =DATE(YEAR(A2),MONTH(A2),1)
Then, format that field as mmm yy, and add it to the pivot table.

Andyroo < wrote:
> I have the same problem. Just because you have formatted the date to
> look right does not mean that Excel will treat it as the month you have
> displayed. I think we both need the same solution - I want Excel to
> store the displayed date of Jan 04, as the same and not a date value so
> that when i use pivot table I get all the entries for a month as one
> line , not multiple lines.
> I agree with Debra bt in my case I want the month and year, like Jan 04
> and not '1'

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/12/2004 10:48:55 AM
Reply:

Similar Artilces:

lookup problem #2
I have a sheet with J11 called Buyers # and K11 called Buyers name. On the next sheet called Buyers there is a list with B1 being the buyers number, and C2 is the buyers names. How can I enter a number on the first sheet and get the corresponding name to come up? Todd Have a look in HELP index for VLOOKUP -- Don Guillett SalesAid Software donaldb@281.com "Todd Fischer" <todd@fischerhome.net> wrote in message news:evMAjQDPEHA.3052@TK2MSFTNGP09.phx.gbl... > I have a sheet with J11 called Buyers # and K11 called Buyers name. On the > next sheet called Buyers there is a...

Publisher Problem
Volunteer Nancy C. Benner Janyszeski Answers Subject MS Publisher 2000 Problem Question I'm using Pub 2000 in on a Pent 4 3.06GHz processor 512 mb ram. Publisher is installed on the local drive (c:). Problem is when trying to open a publisher file from the network share it won't open. The files are on a NT Server 4.0 shared directory with full access to all users. When trying to open the file all it does is close publisher completely. If I do manage to get a file open when I try to save it the message appears stating that the destination drive (S:\shared) is full. An examin...

Problem creating distribution group
I have over 700 address contacts that have been "tagged" into various categories. I know how to set up a new distribution group. However, when I choose "select names" the names from my list apear as first name, last name instead of last name , first name. This makes it almost impossible to scroll up and down looking for the name I want to select and add to my distribution group. Please advise of any suggestions. What version of Outlook? Randal Langley wrote: > I have over 700 address contacts that have been "tagged" > into various categories. I ...

Problem with register scrolling
I just started having this problem with Money 2004 Deluxe. When I try to scroll to the last entry of my register the scroll bar reaches the bottom, but I am not at the bottom of the register. I must click on the down arrow at the bottom of the scroll bar to get the register to scroll to the last entry. bdog Are you using a scroll mouse button? Sometimes the button gets stuck and what you describe happens in Bills & Deposits. If so then move your scroll button a slight amount. Mark Fields "bdog" <bd@op.net> wrote in message news:Xns9469EF048AE96bdopnet@24.50.78.38...

Command button code problem
I have a form with a command button that opens a report in preview. The form has a combo box from which you select the record that should be shown on the report but when I click on the command button I get all the records not just the one I have clicked What's wrong with my code? Here it is Private Sub cmdprintcr_Click() On Error GoTo Err_cmdprintcr_Click Dim strAVCISCode As String 'set strAVCISCode equal to the selected value before closing the form, otherwise it will give us an error strAVCISCode = "AVCISCode" DoCmd.OpenReport "rptCrimereport",...

Table of Contents
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi. I'm a relative newcomer to Word for Mac (and an unschooled user of Word for PCs). <br><br>I'm trying to generate a TOC for an existing 26-page legal document. Heading 2 is a short phrase followed by a period. Paragraph follows. Example: <br><br>5.1. Taxes. Tenant shall have no responsibility for payment of any kind or portion of real property taxes or assessments with respect to the Premises or the real property upon which the Premises are situated. <br><br>When ...

Money Small Business Inventory Problem
I have entered an incorrect value for the purchase of an inventory item. I would like to correct the error, but I cannot find any where to do it. Can anyone help? Thanks Jack ...

Linked Excel table with forced text wrap
I have an Excel table linked to Access. The text in Excel is forced to wrap (I've tried both Alt-Enter and using Char(10) in an expression - same result). Char(10) or Alt/enter code shows up as a little box in Excel with no forced wrap. Any ideas? Here's the text in Excel (shows up as text, not dates) "12/12/05 1/03/06 3/22/06" Here's what I get in Access 12/12/05(little box)1/03/06(little box)(little box)3/22/06 ...

Dynamic union query based on "formulas" in table
Hi, I have a table that looks something like this: Company Product Sales 1 A 5 1 B 2 1 C 7 2 A 8 2 B 1 2 C 2 I want a query to produce a table showing Products A though D which is the sum of Products A and B. Result: Company Product Sales 1 A 5 1 B 2 1 C 7 1 D 7 2 A 8 2 B 1 2 C 2 2 D 9 I have many...

Receiving "Unknown" error opening DB tables (Office 2003 SP3)
I am receiving an error opening tables that only says "Unknown" and I cannot run the create table wizard. Here is what I have done so far: 1. I have reinstalled Office 2003, and upgraded to SP3. 2. The DB is written in Access 2000 and the default file format has been set to Access 2000. 3. Forms, Queries, and Reports all work well. 4. I cannot open a table, run it in design view or run the create table wizard. 5. I have not been able to install KB829558 for Microsoft Jet Pack 4.0. I receive an error saying this hotfix is older than the one installed on the PC. Has an...

Pivot Table Help
I have a column for the actual date and a column for a grade. I hav created a pivot table to total up information in regards to this data. I need to sum up the amounts by the month not the individual day. However I need to keep the individual date as mm/dd/yy. Is there a way that I can manipulate the pivot table to sum up based o the month only? Date 1/2/04 1/3/04 ... 5/6/04 Grade 2 1 ... 5 The pivot table works ok however I really only need to sum up th totals for each month not for each particular day. Is there a way tha I can use the same day column but only calculate the totals...

Moving Emails to Folders
Though I encountered no problems over many months, in the last several I have run into a huge issue in moving certain emails from my "in" and "sent" boxes to my achive folders. These emails all either originate from China or have a Chinese originated reply in the email string. The individuals with whom I'm corresponding in China are from major corporations and, in one case, the person is an employee on my corporate email system. The symptoms are as follows. When I try to drag the email to the folder, the hourglass appears, as if it is trying to complete the transfer. A...

IE 7 and problems with Money 2005
Yesterday my computer automatically updated to IE7 and now I can not sign in online using my MS 2005. I get an "Your sign-in information could not be verified. Go online or select the work offline check box, and then reenter your sign in information" I also am unable to get account updates from the MS Money website. Does anyone know what I need to do to fix this? In microsoft.public.money, Giavid wrote: >Yesterday my computer automatically updated to IE7 and now I can not sign in >online using my MS 2005. I get an "Your sign-in information could not be >verifie...

cell reference problem #2
I am trouble with cell references in a spreadsheet. Some cells will display the cell reference rather than the value it points to. For example the cell will display =A1 as text rather than the content of that cell. What could cause this???????? thanks in advance david Hi David, First check that you have calculation turned on. Tools, Options, Calculation, automatic calculation If that is the problem and it recurs, see my slowresp to see how to spot the problem earlier. The other possibility is that you have Text formatting instead of General or a numeric format. Other things t...

Problem while tring to run a macro designed to save embedded images
Tink@discussions.microsoft.com Please solve me the following problem I'm getting an error as Run-time error '91' Object variable or with block variable not set while trying to run the macro in VBA. Thanks in advance How can you expect anyone to know without seeing your code? Post a message in the newsgroup specifically for general Outlook programming issues "down the hall" at microsoft.public.outlook.program_vba or, via web interface, at http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.outlook.program_vba . And include a small code...

Pivot Table update without opening the Excel file.
Hi, I have a spreadsheet saved as an xlt. It has three pivot tables which connect to CSV data files using Microsoft Text Driver. The CSV files are updated overnight by an external application. The Excel file is in \\Server\Viewers The Data files are in \\Server\Data Local users use a shortcut to open the Excel file on \\Server\Viewers which the does an auto-update from the CSV files. The pivot table data is updated. All that has been working perfectly for years, internally. Now I need to send the same excel file to another company by email. I have succeeded in writing a vbscript that creates...

Character Spacing Problems
When attempting to print in Publisher 2002 my text appears disjointed and expanded. if I use character spacing to correct this it only makes the printed copy worse! The text looks fine of the screen before I print but prints out all weird. Can anyone help me? It isn't a Publisher problem. It's most likely a printer driver problem. Go to the web site of the printer manufacturer and download the latest drivers. -- Ron Cohen drc023@ivwnet.com "Elizabeth" <beth@breadministries.org> wrote in message news:10fd01c34d43$5fdc0c30$3501280a@phx.gbl... > When attemptin...

Distribution List problem #3
Hello, I've created a universal distribution list on a child domain but no one can see that list. But, if a list is created on the parent domain everyone can see it. I can see the list under Exchange System Manager. We are running Exchange 2003 sp2 running in native mode on a Win 2003 SP1 member server. Parent and child domains are both 2000 SP4 running in native mode Than you in advance. - Are you sure you created a new (mail-enabled) Universal Distribution Group in the child domain? - Was domainprep run in the child domain? - Is there a Recipient Update Service (RUS) pointing...

Problem using "Update retirement account" in Money 2005
I'm having trouble updating my 401k account using "Update retirement account" for my end of 2004 statement. There is one specific investement that produces the following error: "... will cause the balance of 'xx' held in this investment account to become negative at some point. Please cancel or revise the transaction." If I try to do the "Dividend" and the "Buy" transactions manually then I get the following error when I try to do the "Dividend" transaction: "You have not purchased 'xx' in this Investment Account...

Pivot for Yes, No and Blank
I have a sheet that is tracking responses to a class and I would like to summarize on a pivot. The sheet has four columns; A = Name B = Invitation Sent C = Invitation Returned D = Attending B, C and D will then have a value of Yes or No or will be blank - How can I create a pivot that will show me how many have been sent, how many have been returned, and how many will be attending? Thanks. Hi IMHO your current layout is not well suited for a pivot table report (you already have a structure quite similar to the pivot table). You can easily add two total columns below this table to sum t...

RPC over HTTP problem #2
I have configured an Outlook 2003 client to use RPC over HTTP over the internet (DSL) with an Exchange mailbox that is over 15 GB in size. When I tested out the connection with a much smaller test mailbox, Outlook launched fine. When I tried to open the very large mailbox, the Outlook client tried several times to connect, but failed. When I then tried to reconnect to my smaller test mailbox, that failed as well. Thinking that something had happened at the server, I went to another machine, I was able to successfully connect the smaller test mailbox. Does anyone know what is happening?...

No leading for list inside table (Word 2007)
I have a simple 2-column table containing agenda items for a meeting. The left column contains the topics. They are formatted as a numbered list. The right column contains the subtopics. They are formatted as a bulleted list. It looks like this: 1. Topic 1 * Subtopic * Subtopic 2. Topic 2 * Subtopic 3. Topic 3 * Subtopic * Subtopic * Subtopic * Subtopic The paragraph settings for both columns are the same: 6 pts Spacing before 0 pts Spacing after Checked Don't add space be...

Help!! I have problem deleting 2500 rows of filtered rows!!!!
I have total of 156 columns and 3900 rows with 20 columns of formula for each row. My filtered result is 2500 rows. I tried to delete 2500 rows in my excel but it took me more than half an hour to delete it either by vba code or manually by excel. I tried using office clipboard manually, it paste the data as value and my formula isn't there anymore. Anybody has any idea how to write a vba program to solve this problem and how to write a office clipboard in vba. Please help... anybody... :( DESPERATE FOR THE ANSWER -- shirley_kee ---------------------------------------------------...

Problem with DPM 2010 download
I try to download the Data Protection Manager 2010 Beta from this web site page:http://www.microsoft.com/systemcenter/dataprotectionmanager/en/us/2010beta-overview.aspx , but it failed. Why? Any help will be grateful, thanks. Try this link.. https://connect.microsoft.com/Downloads/DownloadDetails.aspx?SiteID=840&DownloadID=22070 -- Santhosh Sivarajan | MCTS, MCSE (W2K3/W2K/NT4), MCSA (W2K3/W2K/MSG), CCNA Houston, TX http://blogs.sivarajan.com/ http://www.sivarajan.com/publications.html "Alex.Zhao" <Alex.Zhao@discussions.microsoft.com> wrote in messag...

POP3 Connector problem #3
Hi all, I have the following problem: We are running SBS 2003 SP1. We use POP3 Connector for downloading mails to mailboxes on Exchange server. Users have local addresses in a shape of user@abc.local. Additionally I have set another address for all users: user@abc.cz and set it as default. Then I have tested it and it worked - messages has been delivered. However after about 30 minutes POP3 Connector started to produce a lot of 1019, 1023 and 1035 errors each 15 minutes (the period when it dowloads mails). Mails are downloaded (they aren't at the providers server any more) but are n...