Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Format (Excel 2003)

    I am using a Pivot table to consolidate data that I export to another application. I am having a formatting problem in the table. In the table when the data element in the first column has multiple items to report in the second column I can not get the data in the first column to display in each row.

    I have included an example to try to illustrate the problem. My data is more complicated then this example with multiple columns. In my example name 1 and name 2 has sales for items a, b and c. Very simply, how do I get the

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (Excel 2003)

    You can't do this with a pivot table, I'm afraid. You could copy the pivot table and paste it as values in another location, then run a macro to fill in the missing values. There are examples of such macros in this forum. But you would have to repeat these steps each time the pivot table is refreshed or modified...

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Table Format (Excel 2003)

    It would not be easy to read, esp. if you have a large table. The format presented by the pivot table is a report format that assists readability. What is the underlying reason for doing this??

    A work around is to select the entire table, copy and paste special (values), select the column with the name1 and 2, and then use Edit, Go To, Special, Blanks. Once blanks are selected, type = and the (up arrow), and press CTRL + Enter to fill the blanks with the cell values.
    Regards,
    Rudi

  4. #4
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format (Excel 2003)

    Thanks for the replies. The underlying reason is that he data is to be exported to another application and the data needs to be in each of the records except for the total line which is a different problem..

    On your suggestion of using the go to special blanks you indicate that I should select the table and follow the procedure to fill in the blank cells. When I try selecting the table and filling in the blanks I get the message

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (Excel 2003)

    You cannot use Rudi's idea in a pivot table, you must copy it and paste special as values in another location. Since you need to repeat this each time the underlying data of the pivot table have changed, you might create a macro to copy / paste special and to fill in the blank cells.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format (Excel 2003)

    Have you considered using a sumproduct function to summarize the data? You could have that on a lead tab and the detail on a separate tab, then sumproduct on the combination of Name and Item. Any new names or items could be added and the sumproduct function copied, so growing the list would not be difficult. Have the underlying data defined via a dynamic range name used in the formula and it will grow or shrink as required.

  7. #7
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format (Excel 2003)

    Thanks to everyone.

    I was not able to get Sumproduct to do exactly what I wanted. It looks like the Macro solution will be the best.

    Thanks for the help.

    M

    M

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (Excel 2003)

    The replies in the thread starting at <post#=460022>post 460022</post#> may help.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (Excel 2003)

    Your code selects the first pivot table in the worksheet, it doesn't do anything whatsoever with PR, so you can omit the code to prompt for a cell. Thus the need to check whether the cell is in a pivot table isn't needed.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Table Format (Excel 2003)

    Oh....OK...! What do you know!

    Thanx! I have editted it in the previous thread!
    Regards,
    Rudi

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Table Format (Excel 2003)

    <P ID="edit" class=small>(Edited by Rudi on 28-Jun-05 08:56. Updated the code based on Hans's comments!)</P>Hans,
    I thought this would be a good opportunity to try a macro, so I set up this on the sample file provided in the original post!
    <pre>Option Explicit
    Sub FillBlanks()
    On Error GoTo EH
    ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel
    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlValues
    Columns("A:A").SpecialCells(xlCellTypeBlanks).Form ulaR1C1 = _
    "=R[-1]C"
    Selection.CurrentRegion.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Range("A1").Select
    Exit Sub
    EH:
    MsgBox Err.Description, vbExclamation
    End Sub
    </pre>


    One Q:
    How can I test if the user clicked a cell in a pivot table. The macro works great, but fall flat and does NOT error if a person did not select a sell in the pivot table. If I can't test for a pivot cell, them maybe something in the line of DIM-ing the current region of a search for a Pivot Table and testing if the cell supplied is within the range! Any ideas to debug this problem???

    Tx
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •