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!

 

[JamesCH]

I'm hoping you can help me on this. I have 3 workbooks feeding off of a central workbook that is refreshed daily. Basically, the central workbook pivots data out of a database query. I add formulas on the end of that pivot, then the three workbooks have pivots of the original pivot plus the formulas added to the end.

I need the individuals using those three workbooks to be able to make annotations in two columns next to the pivots. Moreover, I need to have each of those workbooks looking at the annotations of the other two. Is there any way to do this by adapting the code you've already created?

I can provide samples if necessary. All of the Pivots are tabular, with no totals. I have already created a unique identifier for each line, so the code doesn't need to include the creation of a keyword.

 

[JamesCH]

Should clarify: because I already have a unique id for each line and that id is in the same column in all pivots, the code only needs to point to that column as the keyword rather than creating its own.

 

[Jerry Sullivan]

Hi James, Even though your problem relates to annotating pivot tables, the nature of the solution needed to sync comments from multiple user's workbooks makes a problem requiring a very different solution.

Since this thread is so long, I'd suggest you start a new thread for your question. I'll provide some suggestions there if someone doesn't provide you a solution first.

 

[apaled]

I tried your most recent code, and think it's a great solution, unfortunately I'm not able to get it to function entirely. The macro does create the note columns and the notes worksheet but the problem I have is that the notes will not refresh.. if I enter a note in the Grand Total row, this is the only note that refreshes. Any suggestions.

 

[skbelpet]

Hi JERRY

I just registered to thank you for the nice "annotation" code for pivot tables. However I have similar problem to chinmonku_tak ... I created my pivot table (in excel 2013) using data models (I use multiple tables) (I am not sure with excel name for that in english - because my excel is in slovak) and definition relations between them. I tried simple pivot table and codes you provided us. It worked perfectly. But....by using any of the codes (even those for mac) it doesn´t work for data models. I mean pivot table build on data retrieved from multiple sheets (where mutual relationships are defined between them). When I enter comment and than colapse, filter, or make any change to the pivot view....comment disappear and I can´t see it in the table again, after filter or colapse cancelation. Do you think you could help me somehow?. Thank you very much. ....Ps: I am also new in VBA ...so it is not possible for me to do it myself.
Great thanks in advance
Peter

Thanks for sending your file to me. It would have taken a lot of back and forth to figure out why that wasn’t working without seeing your file.

Because your PivotTable was created using PowerPivot, the PivotItems in the RowField can’t be accessed through the PivotCell.RowItems property. It looks like the PivotCell.MDX property could be used instead. MDX stands for Multidimensional Expressions - a query language for OLAP databases.

As explained a few posts ago, the current code builds a key phrase like: "Europe|France|Paris|" using the PivotCell.RowItems collection.

The PivotCell.MDX property will return an expression like: “([Measures].[Sum of Population], [Table1].[City].&[Paris], [Table1].[Continent].&[Europe], [Table1].[Country].&[France]) “

As a quick fix, the code could be modified to convert that expression to the simple “Europe|France|Paris|” that the rest of the code is expecting. Rather than doing that, I think it will be better to rework my code to utilize this accepted standard.

The current code stores data in a two-dimensional matrix, and that has made it difficult to add handling of: added/removed/rearranged fields, report filters, and storing comments specific to a single data item instead of the entire row. Using MDX methodologies has the potential to overcome those obstacles.

I hope to rework that soon. Until then the code in Post #41 appears to work for PivotTables created using PowerPivot. I described that as a version for Mac users because the Excel 2011 object model doesn’t have a PivotCell object. Because of that the “Mac version” also works for PivotTables from PowerPivot.

 

[Amrog]

Hi Jerry ,

I'm referring to post number 55. I pasted your code into a standard module and and the required code for the sheet I would like to annotate. To test your code I entered text notes in a column to the immediate right of my Pivot table. There was no change. Am I missing something, can you assists?

Regards,

Amrog

 

[NorbertFerenczi]

Hi Jerry,

at first thank you for the code, this is exactly what I`m looking for. I did exactly how you described with post number 55, but as by Amrog I do not see any change. If I try to debug the error I receive the following error: "Compile error: Sub or Function not defined" and it reflect on the Check_Setup (Me)? Could you please advice, what I have to change. I tried to insert the code under Module1 (I have several macros under Module1 (VBAProject (PERSONAL.xlsb)and also under a newly created module (Module9), but the result is the same.

Thank you in advance and best regards
Norbert

 

[BORUCH]

Thanks for sending your file to me. It would have taken a lot of back and forth to figure out why that wasn’t working without seeing your file.

Because your PivotTable was created using PowerPivot, the PivotItems in the RowField can’t be accessed through the PivotCell.RowItems property. It looks like the PivotCell.MDX property could be used instead. MDX stands for Multidimensional Expressions - a query language for OLAP databases.

As explained a few posts ago, the current code builds a key phrase like: "Europe|France|Paris|" using the PivotCell.RowItems collection.

The PivotCell.MDX property will return an expression like: “([Measures].[Sum of Population], [Table1].[City].&[Paris], [Table1].[Continent].&[Europe], [Table1].[Country].&[France]) “

As a quick fix, the code could be modified to convert that expression to the simple “Europe|France|Paris|” that the rest of the code is expecting. Rather than doing that, I think it will be better to rework my code to utilize this accepted standard.

The current code stores data in a two-dimensional matrix, and that has made it difficult to add handling of: added/removed/rearranged fields, report filters, and storing comments specific to a single data item instead of the entire row. Using MDX methodologies has the potential to overcome those obstacles.

I hope to rework that soon. Until then the code in Post #41 appears to work for PivotTables created using PowerPivot. I described that as a version for Mac users because the Excel 2011 object model doesn’t have a PivotCell object. Because of that the “Mac version” also works for PivotTables from PowerPivot.

Hi
I’m having the same problem it keeps on asking me if I would like to replace the values

and it repeats it through the pivot tale I’m currently using power pivot, did you ever get a chance to write a new code for a power pivot ?

 

[Jerry Sullivan]

Hi Boruch, No unfortunately, I started rewriting the code and fell into the trap of seeing more and more things I could do to make it better. I put that project on a shelf for a while, but I am retiring in April, and planning to spend some time to develop a nice Add-In.

What is the exact error message your are getting about replacing values?

 

[BORUCH]

Hi Boruch, No unfortunately, I started rewriting the code and fell into the trap of seeing more and more things I could do to make it better. I put that project on a shelf for a while, but I am retiring in April, and planning to spend some time to develop a nice Add-In.

What is the exact error message your are getting about replacing values?

HI

well the major problem is that it is repeating all the notes if you can fix that i would appreciate it

the message that i'm getting is after i filter and UN-filter the power pivot table,it is asking me if i want to replace the values i believe its referring to the column where i store the notes