how to annotate pivot tables?

[miconian]

My users spend a lot of time in large pivot tables that are set up in compact view, expanding and collapsing fields by clicking on the plus and minus symbols. As they do this, they want to make notes about individual line items.

However, this is difficult because a) there is nowhere that allows the user to make notes inside the pivot table, and b) if they make notes outside the table, the row their note corresponds to will change when fields are collapsed and expanded. Also, it seems that Excel does not allow comments to be added to cells within pivot tables.

Surely others have had this problem. Is there some obvious workaround I'm missing?

thanks!

 

[MBBrown]

Thanks, Jerry. This is great!

 

[MBBrown]

The code seems to be recording a blank entry in the "|Notes" sheet once the initial comment/note is deleted from the notes section in the PivotTable sheet. Is there a fix for that as well?

A timestamp feature would be great for my particular project.

 

[notandor]

Is there a way to make this code work for more than 1 pivot table per sheet?
I have 4 pivot tables in one sheet, and it stops as per the initial check in the code.

 

[notandor]

Also, another side question:
Is there a way one can "Change data source" for all pivot tables together in one sheet... or better in the whole Excel file?
Also, is there a way one can "Refresh" data for all pivot tables together in one sheet... or better in the whole Excel file?

I have around 20 in one file spread over multiple tabs.. it gets cumbersome!

 

[faustoruizm]

Hi! Amazing code! This is working just fine!
Is it possible to add more than two notes? I have to make 3 or 4 annotations for each row.

Thank you for sharing your experience with us!

 

[Linda85]

Hi Jerry,
thanks for sharing this code.
I have a small problem. My pivot table has expendable rows. The refresh of the notes only work when the notes are entered at the top level (of each expandable). As soon as I expand the row and put notes on the individual lines, the notes do not refresh. Only the notes on the top row are being refreshed.

The saving of the notes on the new tab however works fine regardless of expanded or collapsed line.
Do you know what could be causing this and how to fix it?

Thanks in advance

 

[Linda85]

Hi Jerry,
I think the root cause for the data not being restored correctly is that France has dates in the pivot but the dates get stored as numbers. For example 18-Sept-12 becomes 41170. I have the same problem with my pivot. As soon as I change to general format, it restores correctly. But my users need to see the actual date in the pivot not the general number. Is there a way the storing could happen in actual date format?

Hi France,

I'm glad that you're interesting in trying this. It's difficult to know what the problem is from your description.
Each time a PivotTable change occurs, the code is supposed to clear the notes then restore them to their new cell locations.
It appears that the code is correctly storing the notes, but not restoring them.

The restore should happen at this part of the Function Refresh_Notes(PT As PivotTable)...

'---Match KeyPhrases for each visible row of PT
    Application.EnableEvents = False
    lOffset = PT.TableRange1.Columns.Count + 1
    
    With PT.RowRange
        For lRow = 2 To .Rows.Count
            sKey = GetKey(rRowRange:=.Cells, _
                lNoteRow:=.Row + lRow - 1, vFields:=vFields)
            vReturn = Evaluate("=INDEX(" & tblNotes.Name & "[Note],MATCH(""" & _
                sKey & """," & tblNotes.Name & "[KeyPhrase],0))")
            If (Not IsError(vReturn)) Then .Cells(lRow, lOffset) = CStr(vReturn)
        Next lRow
    End With

If you have some familiarity with VBA, you could put a Breakpoint in at the first line of code above then use F8 to step through each line to see why those values aren't gettting restored.

There's a chance the code control isn't reaching this point at all, which you could determine with a similar process of stepping through the entire code.

Please let me know what you find out.

 

[azarij]

Hi All,

The code provided here is seriously cool! Respect to the author!
I was playing with the code and have a couple of question re that.
I have a pivot table dynamically updating from raw data sheet. The thing with current VBA code presented here is that it creates the unique key phrase using all pivot table fields in one row.
Is it possible to change the code in such a way that it would only use one pivot table field as key phrase?
Due to the nature of the data I'm processing using this pivot table, one field in it will always be unique, but others are constantly changing.
if it's possible, how do I do that?

I strongly suspect I need to change the following code parts:

'---Build formula to use as Match KeyPhrase

'---Match KeyPhrases for each visible row of PT

but I can't figure out what exactly needs to be changed there.

Any help will be greatly appreciated!


Alex

 

[azarij]

bump

 

[alwayspivoting]

Linda,

I believe you are correct. I was having problems keeping the notes when using a date and everything works when I remove the date column. Fixing the code to allow for dates is far beyond me though!