Pivot Table Format (Excel 2003)

[mmckissock]

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

[HansV]

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...

[Rudi]

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.

[mmckissock]

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

[HansV]

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.

[Torquemada]

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.

[mmckissock]

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

[HansV]

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

[HansV]

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.

[Rudi]

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

Thanx! I have editted it in the previous thread!

[Rudi]

<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