Pivot Notes

Field Names need to be added to the Code settings as a colon delimited String for Indexing the Pivot Table. 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. Here is the Code to establish Data Model Field Names:

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
						

add these names to the Code:

- normal
Private Const PivotTableFieldNamesPT1 As String = "Region:Prod Cat:Sum of Revenue"

- data model
Private Const PivotTableFieldNamesPT1 As String = "[Region_Data].[Region].[Region]:[Product_Data].[Prod Cat].[Prod Cat]:[Measures].[Sum of Revenue]"
                            

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

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

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

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:

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

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

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
						

Pivot Notes can store and sort Formula for Pivot Table Fields and for Pivot Table data using normal Formula or the =GETPIVOTDATA() Function - just as it would if you were writing it in a Column next to a Pivot Table that was not using Pivot Notes. The main difference using Pivot Notes to store Formula is that when you refresh the Source data for a Pivot Table the Formula will persist next to the Pivot Table Row where the Note was stored. The only thing you need to be careful about is not to cause any stored Notes circular references. Let's add a simple Formula to reference the Customer Cell in the Pivot Table, remembering to lock the Column:

Now we will Slice the Table for 2 Weeks and for MINERALS - the Formula still references the correct Customer, "Customer A"

This example luckily did not result in a stored Note circular reference. So what about something a little more complex and what about the circular references. Consider a Formula that counts the number of gaps for any number of Weeks Sales per Customer and idetifies whether they have traded or have been trading intermittently. I will enter the Formula below, locking the relevant Columns and copy it down the Range. Then I will remove the Formula for Sub Totals and Grand Totals. The Formula can be seen below (notice I have changed my Pivot Note Columns using a simple refresh)

If I select Week 38.2016 to Week 42.2016 for just 'Customer E' I now have a view of how each Customer is trading up until Week 42.2016 (there is no data after Week 42 yet in my Source data). You can see 'Customer E' is trading intermittently on 'Product B' and not trading at all on 'Product D'

I can now carry on leaving Notes - the Formula will persist. So, what about the circular references? Excel hasn't reported anthing unusual. Well if I click into the "Notes" Worksheet and double-click a Formula Cell, you can see that indeed the Formula does reference itself. Closing and re-opening the Workbook will cause a circular Formula warning which we don't want

So, how can we fix this. Well the answer is to count how many Columns the Pivot Table uses plus Column A and move our Notes at least that distance away in the "Notes" Worksheet. To start, ensure that you have no Filters on the Pivot Table. Go to the "Notes" Worksheet. Insert a Column and then press "F4" to repeat for as many Columns as you need (I did 18). Press ALT+F8 to enter the VBA Editor and change the Pivot Notes Index Cell to the start of your moved Notes ie. I changed mine to "S1". Now, the Formulas in the "Notes" Worksheet will have changed, but we have a neat trick to update them again. Go back to the Notes alongside your Pivot Table, press "F2" in one of the Notes and press "Enter". All of the Notes will update and we have fixed the circular Formula issue (remember as long as the Notes Formula do not refer to themselves the fact that they are calculating something different on the "Notes" Worksheet is irrelevant)

Please Note:

When adding Data Validation as a List linking to a Range, unless you explicitly link the Range to the same Worksheet as your Pivot Table, then you MUST add the Range specifying the Name of the Worksheet like this:

=Sheet2!A1:A4 ' Excel may remove the Name of the Worksheet when it displays the Address, but it will be recognised correctly as a Range
                            

Data validation is a feature available in Microsoft Excel. It allows you to do the following:

- Make a list of the entries that restricts the values allowed in a cell
- Create a prompt message explaining the kind of data allowed in a cell
- Create messages that appear when incorrect data has been entered

You can add Data Validation for Pivot Note Columns by adding them to one or more of your Note Headers in the "Notes" Worksheet. Here I will add a simple Drop-down list to pick a,b,c in a Cell by adding it to the "commercial" Note Header in my "Notes" Worksheet

Select the Notes Header "Commercial" in my "Notes" Worksheet
On the Data menu, click Validation
On the Settings tab, click List in the Allow drop-down list
By default, the Ignore blank and In-cell Dropdown check boxes are selected
In the Source box, type a,b,c

Now you need to go into the Code and set the UseDataValidation Variable to True

Private Const UseDataValidation As Boolean = True 'False

						

And here is the result of my Data Validation following a right-click, refresh of my Pivot Table allowing me to use a Drop-down Combo Box within my Pivot Notes:

If you want to change the Background Colour of a Worksheet you may find that using Pivot Tables remove the Colour when they resize leaving the default 'No Colour' displaying Gridlines. A good way around this is to use a Background Image

You just need to create a small '.JPG' image about 16 pixels by 16 pixels. I have one here if you want to download and use this one (open the image and use right-click to download or Save as...)

Then simply select the Worksheet that your Pivot Table is on and on the 'Page Setup' Group of the 'PAGE LAYOUT' Tab of the Ribbon click the 'Background' Button. Browse to where you saved the image and select it. The Worksheet will now change Colour to the same Colour as your image. If you have any Styles, these will always over-ride the image and in fact you can still use any kind of Fill to change the Colour of a Cell even with the Background using an image. Now try Slicing or resizing your Pivot Table - it will now have the image as it's background too, neat eh?

A great Feature with Pivot Notes is the ability to use multiple Styles for both the Note Headers and the Note Columns. To do this you simply create the Styles that you want to use for both the Headers and Note Columns and then pass the Names of the Styles as Colon-delimited lists for the Variables below:

Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head1:Pivot Notes head2:Pivot Notes head3"
Private Const PivotNotesStylePT1 As String = "Pivot Notes col1:Pivot Notes col2:Pivot Notes col3"

						

Please Note:

If you don't pass enough Styles for all of your Headers or Note Columns the code will use the first Style as the default for all of the Headers or Note Columns

To create a new Style, first Duplicate a Style by selecting a Blank Cell and then on the 'Styles' Group of the 'HOME' Tab of the Ribbon right-click any Style and select 'Duplicate...'. A Style Dialog will popup and you can set the Parameters for your new Style including a New Name for the Style - the New Name is the Name that you should add to the Variables above. Take a look at the "4.2" Worksheet and the "Notes" Worksheet as I have used the Styles created on the "Notes" Worksheet for the Pivot Note Headers and Note Columns

Remember you can have different Custom Formats for your Styles too, like Dates, Times etc.

You can use Tags to Highlight (by Conditionally Formatting) all or some of the Notes in each separate Column of Notes that you use. You can pass Colours to change the Highlight Colour. You setup how you want Pivot Notes to Highlight Notes by adjusting the Settings in the Global Setup Section of the Code. Tags and Colours should be passed separately and equally ie. If you have 3 Columns of Notes and you want a different Tag for each Column with a different Highlight Colour then you need to pass 3 Tags and 3 Colours like this:

Private Const ConditionalFormatTag As String = ">>:feedback:!"
Private Const HighlightColour As String = "14479865:16183786:15921906"

						

This tells Pivot Notes to use 3 Tags and for each Tag to use 3 Colours. So the first Column of Notes will be Highlighted 'Yellow' if the ">>" Tag is included in a Note. The second Column of Notes will be Highlighted 'Light Blue' if the word "feedback" is included in a Note. The third Column of Notes will be Highlighted 'Grey' if the "!" Tag is included in a Note. If none of the Tags are used the Notes will not be Highlighted

You can also use the built-in Enum Colour Codes:

Private Enum RGBLongColour
    rgbBrown = 3355443
    rgbGrey = 15921906
    rgbGreyHighlight = 15329769
    rgbIceBlue = 16183786
    rgbYellow = 14479865
End Enum

' // pass the Colour Codes like this
Private Const HighlightColour As String = rgbGreyHighlight & ":" & rgbYellow & ":" & rgbIceBlue

						

Please Note:

If the number of Colours or the number of Tags is less than the number of Notes Columns then the Code will use as many Colours & Tags that are passed for all of the Notes Columns

This is useful when you don't want to see the repeated Item Labels as Pivot Notes requires these to build a unique Index to maintain the Notes alongside your Pivot Table. You can use Conditional Formatting to hide the Item Labels. Click in the first Cell below the Headers on your Pivot Table and on the 'Styles' Group of the 'HOME' Tab on the Ribbon, select 'Conditional Formatting', 'New Rule'

Select 'Use a formula to determine which cells to format' and add the Formula below. Adopt this Formula to fit your Range for the Column by changing all occurances of "B15" to the first Cell underneath your Headers - please note any 'Locking' of the Formula for Rows or Rows and Columns as I have done using the '$' symbol

Then press 'Format' and either change the Font Colour to White on the 'Font' Tab or add the Number Format shown below as a Custom Format on the 'Number' Tab to prevent the repeated Item Labels from being visible:

' // add this Formula to the Conditional Format
=COUNTIF($B$15:$B15,B15)>1

' // then either change the Font Colour to White or add this Number Format as a Custom Format to hide the item labels
;;;

						

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 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 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:

' #1 UNPROTECT
ActiveSheet.Unprotect

' #2 UNLOCK RANGE
' - to enable unlock on range
.Range(objPivotTableCustomDocNotesAddress).Locked=False

' #3 PROTECT
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True

						

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

=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

' ## 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"                 
						

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

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