Quick Steps

Pivot Notes documentation

5 Steps to start working with Pivot Notes

  1. Copy all of the Code from the 'Example' Worksheet Code Module in the 'PivotNotes.xlsm' File into your own Worksheet Code Module
  2. Copy the 'Notes' Worksheet which includes the Styles into your own Workbook; alternatively, you can simply rename an exisiting Worksheet to 'Notes' and just Copy specific Styles from the 'PivotNotes.xlsm' File or Copy Styles from my Pivot Notes Style Sheet. You can also use any of the existing Excel Styles
  3. Edit the Setup Section at the top of the Code Module - press ALT+F11 to open the Editor, double-click 'Sheet1(Example)' & scroll down. Adjust the variables for 'PivotTableNamePT1', 'PivotTableWorksheetNamePT1', 'NotesWorksheetNamePT1' etc. (whilst you are setting up Pivot Notes, you can set the 'DebugMode' to True so that the Pivot Notes Code does not fire for any Table actions)
  4. Filter, Slice or right-click to Refresh the Pivot Table
  5. Index the Table by pressing ALT+F8 and run the IndexTable Subroutine

Acknowledgement

Pivot Notes was written by Mark Kubiszyn

  • the latest version is version 5
  • as per terms of the Software License, you should leave the following code attribution in situ

' © Copyright/Author:
' Mark Kubiszyn 2012-2017. All Rights Reserved
' Website/Follow:
' http://www.kubiszyn.co.uk/
' https://www.facebook.com/Kubiszyn.co.uk/
						

Purchase

You can Purchase Pivot Notes for £2.99 via FastSpring by clicking here

Pivot Table Field Names (Data Model & Renamed Fields)

Here I will describe how to get the correct Pivot Table Field Name to use as the Index when congiuring Pivot Notes - failure to identify the correct Field Names will cause errors when attempting to Index a Pivot Table

If someone has changed the Field Name:
If someone has changed the Field Name you can check this by clicking anywhere in the Pivot Table to view the Field List on the right-hand side. If this does not automatically pop up, then you can bring the Field List up by selecting 'Field List' on the 'Show' Group of the 'PIVOTTABLE TOOLS', 'ANALYZE' Tab of the Ribbon. Now check the Field Name in the respective area ie. 'FILTERS', 'COLUMNS' or 'ROWS'. Typically the 'VALUES' area will not be used for Indexed Fields when using Pivot Notes. The Name that you see for the Field will represent the correct Name displayed in the Pivot Table - try it, change one of your Field Names and you will see that the Name changes in the Field List too

If you are using the Data Model:
In order to ensure that you are Indexing the correct Pivot Table Fields when using the Data Model, you can use the following Code to determine the Field Names and then enter the Name into the Pivot Note Field Names Setting ie. "[Region_Data].[Region].[Region]". Press ALT+F8 to enter the VBA Editor and in the Code Module where you pasted Pivot Notes Code add the Subroutine. Click into the Subroutine and press F5. A Message Box will pop up with the Names. They will also be printed to the Immediate Debug Window so you can simply Copy & Paste the Names into the Settings Variable in the Setup section


Sub ListPivotTableFieldNames()
    Dim objPivotTable As PivotTable
    Dim objPivotField As PivotField
    For Each objPivotTable In ActiveSheet.PivotTables
        For Each objPivotField In objPivotTable.PivotFields
            MsgBox "Data Model Field to use in Pivot Notes Settings: " & objPivotField & vbLf & vbLf & "Original Field Name without Data Model: " & objPivotField.Caption
            Debug.Print "Data Model Field to use in Pivot Notes Settings: " & objPivotField
            Debug.Print "Original Field Name without Data Model: " & objPivotField.Caption
        Next objPivotField
    Next objPivotTable
End Sub

						

Here are the results of a Pivot Table that uses the Data Model. The different Names have been highlighted - these are the Names that you need to use when configuring Pivot Notes prior to Indexing:


Data Model Field to use in Pivot Notes Settings: [Region_Data].[Region].[Region]
Original Field Name without Data Model: Region
Data Model Field to use in Pivot Notes Settings: [Product_Data].[Prod Cat].[Prod Cat]
Original Field Name without Data Model: Prod Cat
Data Model Field to use in Pivot Notes Settings: [Measures].[Sum of Revenue]
Original Field Name without Data Model: Sum of Revenue

						

Picking the Index Fields

Choosing how to Index the Pivot Table is very important. You need to remember that in order for Pivot Notes to store and sort Notes correctly it must have a unique Index creating from one or more visible Pivot Table Fileds. If you don't get this bit right, Notes will not be rendered correctly and may appear to move around or not be saved at all. Here are some key points:

1. If possible, choose 1 visible Field that is unique for every single Row in the Pivot Table and Index by that Field

2. If step 1. is not possible then you need to Index by more than one Field in order to create a unique Index. Remember that these must be visible in the Pivot Table so Repeat Label Items should be switched on. If you don't want to repeat Item Labels you can hide the Fields using Conditional Formatting afterwards. Sometimes you may have to Index by 3 or 4 Fields in order to have a truly unique Index

The following is a unique Index for 2 visible Fields, 'Department' and 'Vendor':


Pivot Table
Department	Vendor
A Department	A Vendor
B Department	A Vendor
C Department	A Vendor

Index
A Department/A Vendor/
B Department/A Vendor/
C Department/A Vendor/
						
The following is not a unique Index for the same Fields and will not work as Notes will only ever be left at the first occurance of the repeated Index 'C Department/A Vendor/' and actually due to the way that Pivot Notes renders Notes for multiple heirarchy's, Notes may also appear to move around. In this case another Field is required to Index the Pivot Table correctly:


Pivot Table
Department	Vendor
A Department	A Vendor
B Department	A Vendor
C Department	A Vendor
C Department	A Vendor

Index
A Department/A Vendor/
B Department/A Vendor/
C Department/A Vendor/

						

Please Note: if your Pivot Table has Blanks in the Fields you should 'Exclude' these prior to Indexing the Pivot Table

Local Settings

A Setup Section is provided at the top of the Pivot Notes Code Module that allows you to change any of the local settings for your Pivot Table, Pivot Table Names, Worksheet Names, Pivot Table Field Names, Pivot Notes Styles etc. Here are all of the Pivot Note Local Settings - these apply for each Pivot Table in a Worksheet using Pivot Notes (typically, you will need to edit all of these apart from the IndexDelimiterPT1 which can be left as a Tilde symbol "~"):


' ## the Name of your Pivot Table.  click on the Pivot Table and check the setting for the "PivotTable" Group on the "ANALYZE" Tab of the Ribbon
'    - a check will be made by the Code when Indexing this Pivot Table
Private Const PivotTableNamePT1 As String = "PivotTable1"


' ## the Name of the Worksheet where your Pivot Table is stored.  we will not use ActiveSheet!
'    we need to explicitly refer to this in case of Sheet switching whilst in Cell Edit Mode (F2) and also for error reporting
'    - this prevents the potential loss of Notes if a user decides to go to another Worksheet whilst editing a Note
'    - a check will be made by the Code when Indexing a Pivot Table for this Worksheet
Private Const PivotTableWorksheetNamePT1 As String = "Example"


' ## the Name of the Worksheet where your Pivot Notes will be stored
'    remember you can hide this Worksheet (xlSheetHidden or xlSheetVeryHidden), the Code will still work
'    - you can store Notes for more than 1 Pivot Table in a single Worksheet
'    - Pivot Notes Styles are also stored in the "Notes" Worksheet.  you can Copy this directly into a new Project
Private Const NotesWorksheetNamePT1 As String = "Notes"


' ## the Index start Cell for your stored Pivot Notes
'    typically this will be the first Cell where you have added the Headers ie. Index, Notes 1, Notes 2, Notes 3
'    the Notes are stored in multiple Columns per Pivot Table in the Notes Worksheet specified above
'    - a gap of at least 1 Column should be left between Notes when using more than 1 Pivot Table
Private Const NotesIndexCellPT1 As String = "A1"


' ## the Field Names that will be used as the unique Index for the Pivot Table to link your Pivot Notes
'    ensure that you separate these using a colon ':' if you enter more than one Field Name ie. "Field 1:Field 2" etc.
'    remember to pass these in the order that you want the Index to be created, preferably left to right
'    - there is no longer any requirement to specify an offset, the first Field is where the Code will begin to create an Index
Private Const PivotTableFieldNamesPT1 As String = "Material:Key Figure"


' ## the delimiter that is used when Indexing the Pivot Table ie. "-", ", ", "*", " / ", "~" etc.
'    remember when using Date Fields it may be wise to use a tilda "~" ie. ~03/09/2016~ will allow the Code to split & Index correctly
Private Const IndexDelimiterPT1 As String = "~"


' ## the Style(s) that you want to give your Pivot Note Headers - the Headers for the Notes displayed alongside your Pivot Table
'    - Styles are stored in the "Notes" Worksheet
'    - you can now pass in different Styles for each Pivot Notes Header Column that you are using
'    - these can be passed in using a colon-delimited list ie. "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7"
'    - if you don't pass enough Styles for all of your Header Note Columns the code will use the first Style as the default
'    - when changing this setting retrospectively, you may need to Index the Table again to pull in the New Style
Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7"


' ## the Style(s) that you want to give your Pivot Notes - the Notes displayed alongside your Pivot Table
'    - modify or create your own Styles and then pass the Style Name into the PivotNotesStyle setting
'    - Styles are stored in the "Notes" Worksheet.  you should always Copy this into a new Project
'    - you can now pass in different Styles for each Pivot Notes Column that you are using
'    - these can be passed in using a colon-delimited list ie. "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7"
'    - if you don't pass enough Styles for all of your Note Columns the code will use the first Style as the default
'    - when changing this setting retrospectively, you may need to Index the Table again to pull in the New Style
Private Const PivotNotesStylePT1 As String = "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7"
						
Please Note:
The use of the "PT1" suffix allows you to create new sets of editable Variables when using multiple Pivot Tables on a Worksheet. These settings can be replicated and will be used by Different Pivot Tables using Pivot Notes within this Worksheet - if you are only using a single Pivot Table then you don't have to worry about this

Changing the Index Behaviour

You can change how you would like to Index your Pivot Tables and store Notes by a Variable in the Setup Section called IndexBehaviour. Here is the Variable with its default setting (you change the setting after the '.' full-stop or period symbol):


Private Const IndexBehaviour = IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexes
						

The 3 Options available are as follows every time that you Index a Pivot Table:

  • IndexBehaviourType.xlRemoveAllIndexes all Indexes & Notes will be removed - allows you to start keeping Notes again from scratch by deleting all of the Indexes and Notes
  • IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexes existing Indexes will be retained and any new Indexes appended - DEFAULT: allows you to keep Notes forever, appending Notes regardless of whether they belong to the previous or current Pivot Table Source data
  • IndexBehaviourType.xlRetainSourceIndexesOnly only Indexes matching the Pivot Table Source data will be retained - allows you to only keep Notes that only belong to the current Pivot Table Source data - all older Notes will be deleted

Highlight Notes in Different Colours

Pivot Notes uses Styles for formatting Notes and is designed to allow 1 Colour per Tag per Column, but what if you want to highlight 2 or more different Tags? Well this is possible, but you need to make a couple of changes to the settings and add a snippet of Code. Here I will show you how to change the Code to have a standard light Grey Tag '>>', a Green Tag '#' and a Red Tag '!'. Please Note: these changes will apply across all Columns of Pivot Notes - you cannot simply just change the Cell Colour or formatting because the Styles are overwritten by the RenderNotes() Subroutine

Using the Example Code in Pivot Notes Version 5, you need to adjust the HighlightColour in the Setup Section to explicitly set the different Colours per Column for each Tag:


Private Const ConditionalFormatTag As String = ">>:>>:>>"
Private Const HighlightColour As String = rgbGreyHighlight & ":" & rgbGreyHighlight & ":" & rgbGreyHighlight
						
Now you need to add a bit of Code for each separate Conditional Format you require underneath the first Format (after the 'Else' switch) in the RenderNotes() Subroutine:


                rngFormatAddress.FormatConditions.Add Type:=xlTextString, String:="#", TextOperator:=xlContains

                With rngFormatAddress.FormatConditions(2)
                    ' // only use if the Format requires Priority over all other existing Formats
                    '.SetFirstPriority
                    ' // each global highlight colours
                    .Interior.Color = vbGreen
                    ' // the default Font Colour
                    .Font.Color = vbWhite
                    ' // only set to True if you wish to stop after this Format.  we will apply the default
                    .StopIfTrue = False
                End With
                
                rngFormatAddress.FormatConditions.Add Type:=xlTextString, String:="!", TextOperator:=xlContains

                With rngFormatAddress.FormatConditions(3)
                    ' // only use if the Format requires Priority over all other existing Formats
                    '.SetFirstPriority
                    ' // each global highlight colours
                    .Interior.Color = vbRed
                    ' // the default Font Colour
                    .Font.Color = vbWhite
                    ' // only set to True if you wish to stop after this Format.  we will apply the default
                    .StopIfTrue = False
                End With

						
Now you should be able to Highlight using different Colours for different Tags in Columns:

Pivot Notes showing the Code required to Highlight using different Colours for Tags

Moving a Pivot Table

You can move a Pivot Table that is using Pivot Notes anywhere in the Worksheet without changing any of the settings for Pivot Notes. To do this, click on any Cell within the Pivot Table and select the 'ANALYSE' Tab of the Ribbon. Click the 'Move PivotTable Button' in the 'Actions' Group. Select another Cell in the existing Worksheet and press 'OK'

The Pivot Table will move to the new Cell leaving the Notes on the Right-hand side of the Worksheet. Now right-click->Refresh anywhere on the Pivot Table. The Pivot Notes will be rendered next to your Pivot Table

Please Note: Pivot Notes may not clean up areas that overlap depending on where you Move the Table to (it may be that you are moving the Table somewhere within the Notes Range themselves) - if you see any old Notes, simply select them and clear the Formats. Refresh the Pivot Table again

Pivot Notes Style Sheet

Styles are at the heart of Pivot Notes for rendering Note Headers and Text. You can use my Pivot Notes Style Sheet to Copy Styles into your own Workbooks. This is particularly useful when setting up Pivot Notes from scratch for a new Pivot Table - it means that you don't have to Copy the entire 'Notes' Worksheet from PivotNotes.xlsm, you can just Copy the Styles that you need

Style Sheet.xlsx (11.7 KB)

Using Pivot Notes in Debug Mode

When configuring Pivot Notes or if you are using Pivot Notes and wish to completely redesign your Pivot Table, Pivot Notes will send out lots of errors because it is constantly firing Code, trapping the Worksheet_PivotTableChangeSync() Event Handler. You can switch off Pivot Notes until you are ready to use it by adjusting the 'DebugMode' Setting in the Global Settings section of Setup in the Code to 'True'. This means that everytime an Event Handler is fired the Code will exit without throwing any error messages. The Setting is shown below:


Private Const DebugMode As Boolean = False ' set this to True when redesigning Pivot Tables using Pivot Notes

						

Q & A

Q. Can I use Find & Replace with Pivot Notes?
A. Yes, you can do a Find & Replace to find and replace text within your Pivot Notes as you would normally with a Worksheet. When you do this, the replaced Notes are automatically updated

Q. Can I have 10 or 20 or 50 Columns of Notes?
A. There is no set limit. I have tested with 100 Columns of Notes for a small Pivot Table with no loss of speed. You can have multiple Columns of Notes that use different Styles for both the Headers and Note Columns

Q. Can I supress the Warning Message informing me that: 'There's already data in [Pivot Notes.xlsm]. Do you want to replace it?'
A. No, unfortunately this message is processed before any Code can manipulate it. It is the Pivot Table determining that it needs to resize itself to allow the correct amount of data to be displayed whenever Text is present next to a Pivot Table Field - simply accept the Message by clicking 'OK' and the Table will resize and your Pivot Notes will be rendered next to the newly resized Table

Q. Can I highlight Notes different Colours for specific Tags in all Columns?
A. Yes, you can only use 1 Tag and 1 Colour per Column though - simply pass in the relevant number of Tags and Colours, one of each for every Note Column in the Global settings for 'ConditionalFormatTag' and 'HighlightColour', like this:


Private Const ConditionalFormatTag As String = ">>:feedback:!"

Private Const HighlightColour As String = "14479865:16183786:15921906"
						
This means highlight the first Column when ">>" characters are found yellow, highlight the second Column when the word "feedback" is found in light blue and finally highlight the third Column grey when a "!" character is found

Q. Can I use a UDF in one of the Note Columns like you would a Formula?
A. Yes, you can but you can only calculate what is in one of the other Columns. This is because when the Notes are rendered the UDF's are recalculated which is fine, but if, for example, you were trying to store a Date/Time Stamp this would be recalculated on the Notes Sheet every time. There is a way around Date/Time Stamps whereby you can use some small Code - see the online Help for more Information. Anyhow to allow a UDF simply reference it using a Formula. Suppose that I had 5 Columns of Notes from "N:R". In Cell "R13" I add the Formula:


=AddValues(N13,O13)
						
And then add the AddValues() UDF in a new Code Module (not a Worksheet Code Module) like this - the UDF takes 2 Long Parameters and returns a Long:


Public Function AddValues(cell1 As Long, cell2 As Long) As Long
 AddValues = cell1 + cell2
End Function
						
Now the result in "R13" will be 3 if "N13" + "O13" are 1 and 2 respectively. This Formula including the UDF will be stored in the Notes Worksheet and then rendered next to the Pivot Table. You can then Sort, slice and dice the Table as you want and the values will be correct

Q. Can I use Pivot Notes on a Pivot Table designed in "Compact" View?
A. Pivot Notes is designed for 'Tabular' and 'Outline' Table layouts. You can only use Pivot Notes on a Pivot Table in "Compact" View if all of your Items are unique which would be extremely unlikely but may happen depending on the design of your Pivot Table

Q. Can I Group Items and still use Pivot Notes?
A. Yes you can but if you want to Store Notes at the new Grouped levels, then you will have to re-index your Pivot Table after the Grouping, so it is wise to Group if possible from the outset as you may lose Notes because they will be on different levels. Right-click on the Pivot Table Items and select "Group". Rename the Items how you want them grouped. Once you have grouped your Items, re-index your Pivot Table ensuring you add the new Field into the Pivot Notes Field Names setting

Q. Can I change the formatting for the Pivot Notes & Pivot Notes Headers?
A. Yes you can do both by adjusting the Settings in the Setup section of the Code. You need to use a Style, either a Custom Style or one of the pre-defined Excel Styles. You can pass multiple Styles for each Column of Notes and Headers as a colon-delimited list ':' as the example settings below demonstrate for 3 Columns of Notes


' ## the Style that you want to give your Pivot Notes - the Notes alongside your Pivot Table
Private Const PivotNotesStylePT1 As String = "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7"                       

' ## the Style that you want to give your Pivot Notes Headers - the Headers for the Notes displayed alongside your Pivot Table
Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7"                 
						
Q. Can I Hide the "Notes" Worksheet?
A. Yes you can. Right-click on the Tab and select "Hide". If you want to make the Worksheet very hidden you can do this too - in the VBA Editor set the Sheet Code Module to 'xlSheetVeryHidden'. All changes use Range Objects and do not rely on selecting Ranges

Q. I have Indexed my Pivot Table, but my Pivot Note does not appear to be stored?
A. You need to ensure that your Indexes are unique, for example you may have an Index like: "Total Sales/" in your Pivot Notes once but in your Pivot Table more than once, meaning that any subsequent occurance would never be stored. To avoid this you need to select Report Layout->Repeat All Label Items in the "Layout" Group on the "DESIGN" Tab of the Ribbon and Index your Pivot Table again

Other things to check are:
1. That you are not using a Date in a Pivot Table Field like: '14/03/2015' which may include the '/' backslash character with the '/' Delimiter character in the Setup Code: 'IndexDelimiter1' as the index will not be split correctly. If this is the case, then change the Delimiter to a Tilde '~' like this:


Private Const IndexDelimiterPT1 As String = "~"
						
2. That you haven't changed the Delimiter and then Indexed the Table without removing the original Indexes - this will cause multiple Indexes to be created and your Notes may include a previous Delimiter and therefore will never show next to the Pivot Table. It is advisable, that following an adjustment to the Delimiter that you remove the original Indexes

Q. What options do I have to Email the File out and get people to update Notes or maintain the Notes myself for other peoples changes - can you explain how I can use Pivot Notes in the real world?
A. You have a couple of options when you would like to maintain Pivot Notes for changes where multiple people are involved. Here are the options:

1. You maintain a Master File and update this yourself based upon other peoples changes via Email. For example consider the scenario where you have a large File and only Email out a weekly Pivot Table without the Source to 4 or 5 people. Any of these 4 or 5 people make some comments. They then return their Files to you with their comments. You can then view these and update your Master File of Notes so that it is always representative of everyones comments ready for the next refresh

2. You Email the Original File and a person in each Department make changes, returning the original File. For example consider the scenario where you want to Email around the updated File in some sequence so that different areas of the Business make changes before finally returning the original File back to you by Email

Screen Shots

Pivot Notes showing a single Product Filtered by the 'Material' Pivot Table Field and Collapsed with a Note left underneath the 'Customer Information' Pivot Note Header - this is an aggregated Note showing you what you can do if you want to store Notes at different levels, of course you may only want to store Notes at a single level (this Pivot Table has been Indexed by the 'Material' and 'Key Figure' Pivot Table Fields):

Pivot Notes showing single Product Filtered and Collapsed

Pivot Notes showing a single Product Filtered by 'Material' and Expanded with Notes left next to the 'Total Sales', 'Key Figure' Pivot Table Field underneath the 'Customer Information' and 'Commercial Team' Pivot Note Headers:

Pivot Notes showing single Product Filtered and Expanded

Pivot Notes Unfiltered and showing additional Calendar Weeks of data picked by the Slicer Control. In this Screen Shot the Demand Team have left a Note against Product 17468 underneath the 'Demand Team' Pivot Note Header indicating that there are no sales for the Product against the 'Final Forecast' Pivot Table Field:

Pivot Notes showing additional Calendar Weeks of Data

Support

Support is only given to Purchased Software. You should only contact me on the Support Email address supplied by FastSpring - please provide proof of purchase ie. Email address or Order Reference

Changelog

25.05.2017 - (Version 5)

a) rebuild the Pivot Notes Workbook to be 1 Example Pivot Table Worksheet, 1 Notes Worksheet containing Styles and 1 Data Worksheet this is an effort to makes things more straightforward and ease future issues as I will only be updating 1 Source File

b) go through the Code and Comments tidying up anything that does not make sense or should not be there!

c) add the ability to use Hyperlinks. this is done via a small routine inserted into the RenderNotes() Subroutine. a user may also use a Hyperlink as the Style so that the Cell resembles a Hyperlink - drawback will be a degradation in speed for large Pivot Tables

08.02.2017 - (Version 4.2 & Version 4.2.1 - bug fix)

a) Change to allow Pivot Notes to use a different Style for each Note Column and/or Note Header if required. This is useful not just for visual appearances, but also for Custom formats for Dates, Times etc. Note and Header Style Names are passed using colon-delimited lists

b) Code to add/remove Date/Time Stamps en masse to a separate Column and to lock a specific Column has been added to the Worksheet_Change() Event Handler - this is commented out and can be used by uncommenting the relevant code part(s) as required

c) Add in the ability to fix the Header Row Height. this is useful to emphasise the Headers by making them taller

d) Add in the ability to use Data Validation through the use of a Global Variable. Data Validation should be added to any of the Headers in the "Notes" Worksheet - this will then be transposed down the respective Column next to the Pivot Table. Data Validation can be any type of validation and can include Formula lists from another part of the Workbook Other features of Data Validation can be used ie. when you click into a Cell in the Column a Message can be displayed to the user or you could restrict data input

e) Add in a Global Enumeration Variable to control the Indexing behaviour whenever the Pivot Table Source data requires refreshing:

i) xlRemoveAllIndexes = all Indexes & Notes will be removed
(allows you to start keeping Notes again from scratch)

ii) xlRetainOldIndexesAndAppendNewSourceIndexes = existing Indexes will be retained and any new Indexes appended
(DEFAULT: allows you to keep Notes forever, regardless of whether they belong to the previous or current Pivot Table Source data)

iii) xlRetainSourceIndexesOnly = only Indexes matching the Pivot Table Source data will be retained
(allows you to only keep Notes that only belong to the current Pivot Table Source data)

f) Speed increase for larger tables when updating Notes. Now only Dictionary Indexes for the Target area of Notes are built which are queried against a Notes Dictionary of Indexes - if they exist then only the Taret area of Notes are updated



16.12.2016 - (Version 4.1) A change to the IndexPivotTablePT1 Subroutine due to speed issues and complexity / ambiguity when a user Indexes a Pivot Table. Now a Pivot Table is Indexed as viewed / used and it is left to the user to switch on Sub totals and Grand totals should they require them to be Indexed during the process. Also, change Time() to Timer to ensure that a unique value is calculated for whenever there are many blank Rows as in the case of Indexing an Outline style Table for a number of Fields. 2 NEW Example Worksheets have been added to demonstrate using Pivot Notes with an Outline layout and a Compact layout

25.09.2016 - (Version 4) leaner & meaner, complete re-write and code optimisation (3 Subroutines). I have heavily commented all of the Code. New features include multi-column notes, improved error trapping / handling and R1C1 annotation allowing you to store and Sort Formula alongside Pivot Tables. Many of the changes below are now redundant, although the functionality is still retained and / or has been improved - they are left in situ for legacy versions



24.08.2016 - (Version 3.1.1) TransposeDictionary replaces the WorksheetFunction.Transpose to allow greater Items limit and Pivot Notes > 255 characters

16.04.2016 - (Version 3.1.0) Add the Setup Constant 'PivotTableColumnsOffset1' to allow an offset to be specified when Indexing a Pivot Table. In normal operation, this is left as 1 (one, the first Field), however if you wanted to Index a Unique Column ie. PO Number and that Column is a Field that is 4 Columns along the Fields in a Pivot Table then you could specify the offset as 4 and the Indexing would begin from there. This allows you to Index a single Unique Column if required, the only caveat being that it MUST be Unique! The amend includes adding an additional Variable to the IndexPivotTable(), UpdateNotes() & RenderChanges() Subroutines

10.02.2016 - (Version 3.0.2) Code amend to skip an error in the RenderChanges() Subroutine when a user dynamically adds extra Columns in a Pivot Table Pivot & the routine attempts to clear the Range within the Pivot Table itself

26.01.2016 - (Version 3.0.1) Code amend to remove Resize when transposing arrays. The Transpose Function has a 255 limit on String Lengths stored within a Variant Array so Pivot Notes are limited to 255 characters only but can include Carriage Returns etc.

14.12.2015 - (Version 3) General release. Change the Dictionary object to late binding. Fix incorrectly declared Variable to have suffix of 1 ie. 'PivotTableFieldNames1'. Added Global Variables to use Conditional Formatting, a Conditional Format Tag & the highlight Colour. Notes are now highlighted automatically via the "*" Tag



03.11.2015 - (Version 2.1.1) Add the ability to auto-fit Rows & set the default Column Width when using Word-wrap. Add a Conditional Format Modification to allow a single dynamic Conditional Format to be used to highlight Pivot Notes containing the string "*" (anything or a specific Tag ie. ">>")

23.10.2015 - (Version 2.1) Code change. Add the easy Setup Variables section

22.10.2015 - (Version 2) Re-write, all code now resides in a single Worksheet Code Module



16.10.2015 - (Version 1) Released for beta testing