[SOLVED] How do I get Month Names to show up in my Pivot Table?

mrgillus · #1 07-28-2009, 04:21 PM

After a lot of tweaking I managed to get the Number of the Month to display- For about 40 minutes I hassled with this to stop it from displaying each individual day. Now that I got it by month it is by labeled according to the number month.. 1, 2, 3, etc. How can I get this thing to display the month?! January February, etc.. I'm new to Pivot and am still stumbling over how this works..

Thanks for all the help!

mrgillus · #2 07-28-2009, 04:27 PM

Also, If I have added a conditional formatting to a pivot table and data is added, how do you get it to automatically extend the formatting down the rows? e.g. MOD(ROW(),2 to alternate line colors..

DonkeyOte · #3 07-28-2009, 04:44 PM

Suggestion...

Create a Named Range:

Name: _PTData
RefersTo: ='Data Entry!$A$1:INDEX('Data Entry'!$L:$L,MATCH(9.99999999999999E+307,'Data Entry'!$B:$B))

Alter your PT Source Range to be: =_PTData

On the PT itself, remove "Month" from Column Field and replace with "Date Opened", right click on any given date and select Group -> select both Year and Month, this grouping should result in your desired output (based on my interpretation)

mrgillus · #4 07-29-2009, 08:19 AM

Thanks for your help Donk... I did as you said and that works perfect for the date.. However, it still doesn't appear to be carrying the conditional formatting down in the pivotable whenever I add new data.. Was that formula supposed to help that or was it just something to better than just manually selecting the range with the data in it?

Thanks again,

mrgillus · #5 07-29-2009, 08:37 AM

Please forgive me for the Barage of questions I have about this.. I am trying to figure this out on my own... For every letter in the Work Center there is a name associated. e.g. "K" would be subcontractors.. Can I incorporate the Names of what each letter is into the pivot table? Basically a custom row in the pivot that I can write a formula to index the list of letters with the corresponding name..

Thanks

DonkeyOte · #6 07-29-2009, 08:48 AM

Quote:

However, it still doesn't appear to be carrying the conditional formatting down in the pivotable whenever I add new data.. Was that formula supposed to help that or was it just something to better than just manually selecting the range with the data in it?

Re: conditional formatting... things get a little trickier here given your PT's can move, expand/contract etc... (at least theoeretically), as to whether or not there's a watertight method for doing this I don't know, I'm no PT expert myself I'm afraid... the below approach is to use the VBA Pivot Table Update Event such that as the PT is refreshed the formatting is reapplied such that even rows within the DataBody Range are formatted, obviously if you have historic rows that no longer form part of the PT (ie has contracted) you may end up with legacy formatting issues (ie rows formatted that should no longer be formatted...)

Code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rngData As Range, lngRow As Long
If UCase(Target.Name) = "PIVOTTABLE1" Then
    Set rngData = Target.DataBodyRange
    rngData.Interior.ColorIndex = xlNone
    For lngRow = 1 To rngData.Rows.Count - 1 Step 1
        If lngRow Mod 2 = 0 Then rngData.Rows(lngRow).Interior.ColorIndex = 15
    Next lngRow
    Set rngData = Nothing
End If
End Sub

mrgillus · #7 07-29-2009, 09:07 AM

I appreciate your help... Yeh I am definitely not a fan of these pivot tables.. They do seem to have a very limited ability to format and even if I center the text in the cells, as soon as a new cell is added it goes back to left alignment.. arrgghh!

I'll see what I can do with this..

Thanks again for your help though. I have learned a lot more than when I started about these tables

DonkeyOte · #8 07-29-2009, 09:08 AM

Quote:

For every letter in the Work Center there is a name associated. e.g. "K" would be subcontractors.. Can I incorporate the Names of what each letter is into the pivot table?

To keep things simple, based on your latest layout on sheet 'Data Entry' (which is a lot better incidentally!) I would advise you do something along the lines of the below:

1 - insert a new sheet - named say "LOOKUPS"
in Column A insert the various Work Centre Codes, in B the associated values (ie subcontractor)

2 - go back to 'Data Entry' sheet, add a further calculation column
M1: Work Center Desc.
M2 =IF($G2="","",VLOOKUP($G2,LOOKUPS!$A:$B,2,0))
copy down for all rows

3 - revise your RefersTo Range for Defined Name: _PTData such that reference to $L:$L becomes $M:$M

4 - go to your Pivot Table, Refresh - then right click and Select the Wizard...

click Layout:

a) drag the new Field (Work Center Desc.) and place below Work Center in the Row Field
b) double click on each of the 2 ROW Fields (Work Centre, Work Centre Desc.) and set SubTotal to None

then click OK and Finish

DonkeyOte · #9 07-29-2009, 09:14 AM

Quote:

Originally Posted by mrgillus

...I am definitely not a fan of these pivot tables..

Shame, I personally believe Pivot Tables should be the very first thing that anyone who uses XL on an even a weekly basis should learn to use... they enable the user to generate high-end analysis very quickly without requiring any extensive knowledge of formulae etc... and generally speaking they are pretty efficient (ie much more so than replicating the same analysis using formulas) ... they also encourage people to store their data correctly... ie show the value to be gained by adopting good data storage practices.

mrgillus · #10 07-29-2009, 09:22 AM

Quote:

Originally Posted by DonkeyOte

Shame, I personally believe Pivot Tables should be the very first thing that anyone who uses XL on an even a weekly basis should learn to use... they enable the user to generate high-end analysis very quickly without requiring any extensive knowledge of formulae etc... and generally speaking they are pretty efficient (ie much more so than replicating the same analysis using formulas) ... they also encourage people to store their data correctly... ie show the value to be gained by adopting good data storage practices.

No I totally agree with you there! It is very streamlined and really was able to setup this data the same way I had it in a fraction of the time and is also much more reliable. I just don't like the fact that it is not retaining any of my formatting.. Bold, Center align... I'm sure there is a way to do it and once I figure that out

I will more than likely love these things.. They really are very impressive in their abilities to sort and work with data!

mrgillus · #11 07-29-2009, 09:28 AM

Quote:

Originally Posted by DonkeyOte

To keep things simple, based on your latest layout on sheet 'Data Entry' (which is a lot better incidentally!) I would advise you do something along the lines of the below:

1 - insert a new sheet - named say "LOOKUPS"
in Column A insert the various Work Centre Codes, in B the associated values (ie subcontractor)

2 - go back to 'Data Entry' sheet, add a further calculation column
M1: Work Center Desc.
M2 =IF($G2="","",VLOOKUP($G2,LOOKUPS!$A:$B,2,0))
copy down for all rows

3 - revise your RefersTo Range for Defined Name: _PTData such that reference to $L:$L becomes $M:$M

4 - go to your Pivot Table, Refresh - then right click and Select the Wizard...

click Layout:

a) drag the new Field (Work Center Desc.) and place below Work Center in the Row Field
b) double click on each of the 2 ROW Fields (Work Centre, Work Centre Desc.) and set SubTotal to None

then click OK and Finish

Oh Geez.. I didn't see that post till just now! Got that setup and that works awesome.. I think maybe I am starting to like this a bit better..

Cool

Welcome to the Excel Forum

Forum Statistics