Pivot Notes 8

Purchase

Introduction

Introducing Pivot Notes 8

Pivot Notes 8 · Store, Filter and Sort, Multiple Columns of Notes or Formula alongside multiple Pivot Tables in an Excel Worksheet (Version 5 is included in the Purchase File)

It's been a long time in the making, but it's finally arrived! Pivot Notes 8 replaces all other Pivot Note versions. This version of the Software is faster, smarter and easier to use. Faster, code optimisation using an Interface Type - the number of subroutines have been minimised to just two. The various types of matching are more appropriately targeted within these subroutines. Simple, less setup information for users to edit. Notes can be added at any level. Smarter, Indexes and Notes are added dynamically as and when they are created by the user entering Text or Copying / Pasting Text. Indexing tables is no longer required. Applying Pivot Field Names as Indexes is no longer required. Pivot Table Layouts can be dynamically swapped in real time as you redesign your Pivot Table

Pivot Notes 8: Perform actions on your Pivot Table using a sort, filter or slice to watch your Note Columns stick like 'glue' to your Pivot Table

If you liked this Project, you may also like:


4 Row Pivot Table

Prerequisites

  • Excel 2010, 2013 (Version 5) & 2016 (32bit or 64bit, Version 8)
  • Basic to moderate Excel Skills with the ability to Copy Code into the Worksheet Code Module for the Pivot Table that you want to store Notes for
  • Windows PC · NOT a Mac!
  • No support is provided for customization of this Software

Features

  • Easy setup - Copy the Code, adjust a handful of Constants and play with your Note Headers and Columns to tweak the Styles that suit you best
  • Indexes do not need to be maintained - you no longer have to maintain a list of your Pivot Table Fields for indexing, version 8 builds Indexes dynamically as and when they are required
  • You can still Select your own Unique Fields to use as an Index should you choose and in Build 8.5 I demonstrate how to do this using a Dual Index for 2 Unique Fields on a 4 Row Pivot Table
  • Duality - Pivot Notes allows more than 1 Pivot Table to share the same Pivot Notes
  • Conditional Formats with Tags can also coerce any additional styling that you may want on a Notes Column. You can use multiple Conditional Format Tags, Fill and Font to overlay Styles ontop of the exisitng Pivot Note Styles
  • Edit the EDITABLE SETTINGS to apply an Autofilter to your Pivot Note Range to make it 'sticky' to filter Pivot Notes, Note Columns. You can even Sort these Columns. Then, when you perform another Pivot Table action and then they will return to their original state
  • You can easily Move the Pivot Table and Notes. Move the Pivot Table as you normally would by selecting it and clicking Move Table. Then just perform an action on the Table, click a Slicer, filter a field or Expand / Collapse a Node
  • Pivot Table Source Data can be any Range but it is best to use Tables
  • You can use Blank lines in your Pivot Tables, however, if you enter Text on a Blank line then it will be repeated down your Pivot Table
  • Store Notes for multiple Pivot Tables on multiple Worksheets
  • Leave Notes against Grouped Pivot Fields
  • Use Power Pivot Tables and Queries
  • Concatenate 2 Unique Fields in your Source Data and use that Field as the Pivot Notes Index
  • Apply Data Validation lists to your Pivot Notes Columns
  • Use Find & Replace on any of your Pivot Notes
  • Use the Classic Pivot Table Layout (enables dragging of the fields in the grid)
  • Add multiple Columns of Styled Notes alongside your Pivot Tables
  • Dynamic Row and Column Sizing. Headers and Notes can have multiple Styles
  • Enter double-line Notes using ALT+ENTER
  • Use on Pivot Tables with additional Column Ranges (Rows in the Header where extra values have been used in Columns)
  • Filter, Slice and Sort Pivot Table Fields
  • Supports all Pivot Table Layouts
  • Use existing Styles or create your own
  • Pivot Notes are Indexed and stored at all levels
  • Pivot Notes does not interfere with any additional Custom Formatting of a Pivot Tables Columns or Value Fields, for example Cell Fill Formats or Font Formats
  • Pivot Notes can be stored in the same Worksheet as the Pivot Table
  • Formulas and UDF's can be used to refer to Cells or Pivot Tables ie. =GETPIVOTDATA()
  • Hyperlinks can be used with Pivot Notes to refer to Cells, Email Addresses, Webpages or Filepaths. Visited links will reset (change Colour) when an action is made to the Pivot Table
  • Code Inspection - RubberDucked! (2.5.0.5244)

Limitations

  • Blank Rows can be added to Pivot Tables, however Notes cannot be stored against them
  • Indexes must still be unique. If not, then the first Index is used to store the latest change to a Note
  • Favours Pivot Tables with less Pivot Field Columns, the more Columns, the longer the Index
  • When using Hyperlinks for Pivot Notes the Text cannot be different for links to places and must include a '!' with Cell Reference. For Webpages, the Text must include 'www'. For Email Addresses the Text must include '@' and for FilePaths the Text must include '\'. Using Hyperlinks, Conditional Formatting or Data Validation will also add extra over-head to Pivot Notes which is not noticeable for smaller Pivot Tables
  • Using Undo. Following any editing of a Note, be that copy, edit, delete, you will not be able to Undo a VBA rendered Code change as per design (the general consensus is there is no way of maintaining or restoring the undo stack after a macro has been run)

Installation

PivotNotes_v8.zip is ready to use. Download the File after purchase from the link provided. Right-click on the Zip archive and extract all of the Files. Open the File of your choice using Excel. The now deprecated, legacy Version 5 (original) is also included in the Archive - a link is provided here for Version 5 online Documentation if you still wish to use this version for older Excel versions. For Excel 2013/2016, open the latest Version, PivotNotes_v8.xlsm

Usage

Playing around with the Worksheet Examples

There are now 7 examples in Pivot Notes 8. Example 1 is a straightforward single Pivot Table that uses Pivot Notes. Example 2 (Multiple Tables) demonstrates using multiple Pivot Notes with multiple Pivot Tables. Example 3 (Links, Tags & Val.) demonstrates using Hyperlinks, Conditional Formatting and Data Validation with Pivot Notes. Example 4 (Protection) demonstrates using Pivot Notes with Worksheet Protection. Example 5 (Unique Index Code) demonstrates using a separate Pivot Notes Code to allow you to Index specific Fields in your Pivot Table, aka, legacy Pivot Notes 5. Example 6 is a Pivot Table using the Classic Pivot Table Design. You can change the design of your Pivot Table by right-click, Display and ticking the Classic Pivot Table Layout (enables dragging of fields in the grid). Example 7 demonstrates using 2 Unique Fields and concatenating them using a simple Formula in the Pivot Table Data Source. This Field is then used for the Index by Pivot Notes to store its Notes

Click on each example Worksheet and play around with the Pivot Tables to get used to the Pivot Notes concept. Slice the Tables, add some Notes and then Sort the Notes. Try Collapsing and Expanding the Table Nodes to see the Notes 'Pop' in and out of nowhere. Add extra Data to the Table on the Data Worksheet. Try Right-click->Refresh to refresh the Source data. Try changing the Layout of the Pivot Table dynamically to see how adding Notes happens with different Layouts. Click on the Notes Sheet. This is the Sheet where your Notes are stored. You can use one Notes Worksheet or multiple Worksheets. I use the same Notes for 2 Pivot Table examples and then different Notes for different Pivot Tables. Try changing the Style for the Notes Headers or Columns and then slice the Pivot Tables again to see what happens! Try adjusting the Row Header Heights or the Notes Column Widths and again, slice the Pivot Table to see the changes. Pretty neat, eh? Good, so now let's move on to helping you setup Pivot Notes with your own Pivot Table

Setting up Pivot Notes 8 for your Pivot Table(s) - 5 quick steps

[1] Press Alt+F11 or View Macros to go into the VBE Code Editor. Click on the Sheet1 Code Module. Select all of the Code and then Copy and Paste this Code into your own Worksheet Code Module for any Worksheets where you want to use Pivot Notes

[2] Configure the EDITABLE SETTINGS Section at the top of the Code Module. Add the name of your Pivot Table or Pivot Tables. For multiple Tables you need to delimit these using a pipe '|' ie. "PivotTable1|PivotTable2". Do the same for your Note Sheet or Note Sheets - you can call them any legitimate naming convention you like ie. "Notes|My Lovely Notes". Enter the first Cell for the Notes Index ie. "A1" or for multiple Notes "A1|F1" etc. Here is the standard configuration for the Example1 Worksheet for a single Pivot Table using Pivot Notes

Private Const PivotTableNames As String = "PivotTable1"
Private Const PivotNoteSheetNames As String = "Notes"
Private Const PivotNoteIndexCells As String = "A1"

[3] Select the Worksheet that you are using for your Notes for the Pivot Table that you want to use Pivot Notes with. Include the word Index and then enter your Note Headers (required! - see the image below for an example). Style the Headers as you want using the Cell Styles on the Styles Group on the Home Tab of the Ribbon - these will then be replicated next to your Pivot Table. Style the next Row down as you want - these will be replicated for all of your Notes in each Column that sit next to your Pivot Table (you only need to Style the next Row down). Set the Column widths as you want - these will be replicated for each Column. Set the Row Heights as you want - these will be replicated for all of the Rows



[4] Right-click and choose Refresh to style your Notes alongside your Pivot Table (you can also click a Slicer Button, filter a Field or Expand / Collapse a Node)

[5] Type a Note or Copy & Paste Notes into any of the Pivot Note Cells alongside your Pivot Table

Additional Settings

There are a few additional Optional settings that you can configure. The first ones toggle the settings on/off. So to use Hyperlinks for example change the 'PivotNotesHyperlinks' from 'False' to 'True'. The same applies to Conditional Formatting and Data Validation. If you use Conditional Formatting then you should supply Tags that you will use to change the Fill or Font Colours of your Notes. The Tags can be pipe-delimited ie. ">>|:" meaning that a Note will be formatted whenever you use a '>>' or a ':' in a Cell. They will be formatted according to the Font Colours and Format Highlight Colours that you should also pass. Remember to pass these in sets ie. if you use 2 Tags, then you should supply 2 Font Colours and 2 Format Highlight Colours. To Conditionally Format all Text whenever it is entered into a Cell use an Asterisk '*' in the Tags. Conditional Formatting is set up to use xlContains without Code modification. Colours can be Long values passed as a pipe-delimited String or as Enumeration (Long Colours can be found by entering an RGB value into the Immediate Window: ? RGB(0, 0, 255)). They can also be VBA constants ie. vbBlack & "|" & vbYellow. They can also be the Enumerations that I have included at the top of the Code Module ie. GoodGreen & "|" & NeutralYellow. Tip: add your own Colours here to use with Pivot Notes. To use Data Validation, turn on the setting and then pass your lists as sets with a Column that you want to use for Data Validation and then the List itself ie. 5 (meaning use this in Column 5) = (the separator) I love chocolate, I hate chocolate (the list, separated by commas). Lastly Pivot Notes allows you to maintain the Autofilter for 1 Pivot Table by making it sticky. Turn this on by setting AddAutofilter to 'True'

Private Const PivotNotesHyperlinks As Boolean = False
Private Const PivotNotesConditionalFormatting As Boolean = False
Private Const PivotNotesDataValidation As Boolean = False

' can also be * (asterix, will highlight any Text entered into a Cell)
Private Const ConditionalFormatTags As String = ">>|:"


' or 22428 & "|" & 22428 or "24832|22428" or concatenated VBA Constants ie. vbBlack & "|" & vbYellow
Private Const ConditionalFontColours As String = GoodGreen & "|" & NeutralYellow
Private Const ConditionalFormatHighlightColours As String = GoodGreenHighlight & "|" & NeutralYellowHighlight

' pass Column numbers and lists ie. 2=Yes,No
Private Const DataValidationLists As String = "3=Y,N|4=I love you,I hate you,You are okay"


' add Autofilter
Private Const AddAutofilter As Boolean = False

Screen Shots from Pivot Notes Version 8

A Pivot Table using Pivot Notes

A Pivot Table using Pivot Notes with Additional Column Fields

A Pivot Table using Pivot Notes with Data Validation and Hyperlinks

FAQ

Q. Can I use Pivot Notes with Data Validation?

A. Yes, turn on the DataValidation in the EDITABLE SETTINGS (Private Const PivotNotesDataValidation As Boolean = True) and then pass the Column and List or sets of Columns and Lists to allow Data Validation in Columns within your Pivot Notes ie. "3=Y,N|4=I love you,I hate you,You are okay"

Q. Can I use swap the order of my Notes Columns?

A. Yes, simply rearrange them on the Notes Sheet in the order that you want and then perform an action on the Pivot Table ie. Collapse or Expand a Node to render your rearranged Notes

Q. Can I Index specific Pivot Table Columns?

A. Yes, but due to the slight overhead this will induce the change to the Code is supplied in the Code Module 'Sheet8' for the 'Example5 (Unique Index Code)' Sheet where I Index by the unique fields Region & City. Copy this Code into your Worksheet Code Module and edit the Setup like this: Private Const PivotTableUniqueFieldsToIndex As String = "Region|City" ' or "Region|City|Region2|City2" etc.

Q. Can I Swap Pivot Table Columns?

A. You have to have the Classic layout enabled in pivot table options to be able to drag fields around, so yes, it is possible, but due to the original design of Pivot Notes 8, the Notes will not be rendered correctly using a Classic Table Layout. I have logged this for a future build and as a Bug Fix

Pivot Notes Version 8

This is a video of Creating a Pivot Table and then adding Pivot Notes 8 to a Worksheet Code Module to allow notes to be kept alongside a Pivot Table · view this Holiday Planner video on YouTube

Support

Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support for my Software

  • Responding to questions or problems regarding the Software and its features
  • Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include

  • Customization and installation services
  • Support for third party software or ANY kind of development whatsoever

Before seeking support

  • Make sure your question is a valid Software Issue and not a customization request
  • Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
  • Ensure that you access to the VBOM is allowed and that Macros can run in Excel
  • Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
Remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Future Builds

* Examine the possibility of using dual storage to get Hyperlinks working better

* Further Code optimisation

* User asked to be able to maintain Interior Colours of the Value Fields in a Pivot Table when using Pivot Notes - explore

* User request for Pivot Notes on the left-hand side of a Pivot Table

* Enable the use of the Classic Pivot Table Layout (partial fix 14.09, full fix 24.04.2022)

Bug Fixes

* 01.11.2020 - There is an issue with the Classic Pivot Table Layout where Notes are not rendered. Status = Closed
:= FULL FIX 24.04.2022 version 8.5

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first

24.04.2022 - (Version 8) Build 8.5

Removed superfluous Named Ranges from the Project

Added a Single Index example (No. 7) to the Project that creates a Concat of 2 Unique Fields in the Source Data as an Index that is then used for a 4 Row Pivot Table - this way you only need to tell Pivot Notes to Index on a single Field

As part of a further investigation (13.09.2021 - (Version 8) Build 8.4 for Label Rows and Pivot Tables with different design layouts) I have made a slight tweak to the Code in all Modules to ensure that the Notes sit exactly below and adjacent to the Headers no matter what style and design layout the Pivot Table has for its Labels and Fields Rows. This was achieved using Code found on stackoverflow by Dirk Reichel which grabs the actual Headers Range for a Pivot Table, modified to then give the Row Count

Formula: = Intersect(.Parent.Range(.TableRange1.Row & ":" & .DataBodyRange.Row - 1), .TableRange1).Rows.Count
URL: https://stackoverflow.com/questions/37922713/excel-vba-pivot-table-fill-header

13.09.2021 - (Version 8) Build 8.4

Due to the open issue around the 'Classic' Pivot Table layout, I have Replaced '.ColumnRange.Rows.Count' with '.DataLabelRange.Rows.Count + 1' to pick up the additional Label Rows. Further investigation is still required to ensure every scenario works. Any future build will address this issue once again

21.11.2020 - (Version 8) Build 8.3

Due to a bug reported by a user I have Replaced the .Formula2R1C1 with .FormulaR1C1

11.11.2020 - (Version 8) Build 8.2

A duplicate line of Code that cleared the Formats of the Pivot Notes Range was also causing a Pivot Table with additional Cell or Font Formatting to lose these Formats when Sliced or Filtered - this line has been commented out

03.11.2020 - (Version 8) Build 8.1

Added the ability to just Index specific Fields but as a separate piece of Code as any kind of additional checking adds a slight overhead to the Code
Using this Code though, means that you can maintain Indexes on single Fields or any amount of Fields that are visible as RowRange Items that you like. This can be handy when Pivot Table Source changes include Field Name changes that may affect your Indexes. Therefore by maintaining say 2 unique Indexes that will always be in the Pivot Table, then these Notes will always be returned
This can also be useful for very large Pivot Tables, giving much smaller Indexes, however one must always be aware that the Fields must give a unique Index

26.10.2020 - (Version 8)

Version 5 is now deprecated. Versions 6 and 7 are obsolete. Pivot Notes 8 replaces all other versions released
Pivot Notes 8 is smarter, faster and simplier
Indexing has been removed and Notes and Indexes are added dynamically. Layouts can be swapped in real time. Styles are used extensively throughout to speed up Note formatting