Pivot Table Formatting

[uscarioca]

I am trying to format my row headings to left justified, however each time for instance that a click on the Column drop down and hit OK or Cancel my formatting my row headings change back to Center Justified? I have tried the Perserver Formatting under Table Options as wells as I have Enable the Section and then applied the formatting change, but it does not stay?? What am I missing here? thanks in advance!
Chris

 

[BrianB]

It is a waste of time trying to format PTs.
My standard practice is to just use the PT as an interim step between raw data and my nicely formatted reports.

Transfer data by making an extra column to the left of the PT with =CONCATENATE() formula to make unique lookup values and use =VLOOKUP() in the report (hidden lookup values in column A).

 

[uscarioca]

Brian...I appreciate your response however I am not sure if I get what you are saying. Yes I understand what the Concatenate and Vlookup functions are....

 

[Tom Urtis]

Not sure if this will help but try it and see. What this will do is merge and center the row headers as you filter the fields.

For the worksheet that holds the pivot table, right click on that sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.PivotTables(1).MergeLabels = True
Application.EnableEvents = True
End Sub

 

[BrianB]

Concatenate and Vlookup

We need a way to use Vlookup from a formatted report to a pivot table which requires a unique lookup value. As they do not exist naturally we have to manufacture them in both sheets.

Example
We need to use 2 columns to the left of the PT. So let's say the PT leftmost column is C. Unfortunately it contains blanks. So column A needs a formula like (with first PT row 5) :-
=IF(C5="",A4,C5)
We can now make our unique lookup values in column B with formula :-
=CONCATENATE(A5,D5)

They can look like gobbledegook, but so what. They are nicely in place to be included with the PT range as a lookup table.

These keys can now be copied to, say, column A in another sheet, where they can be referred to by =VLOOKUP(). We now have freedom to format the report exactly as required without being constrained by the PT format.

Hope this helps.