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

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
  • 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
  • Apply Data Validation lists to your Pivot Notes Columns
  • Use Find & Replace on any of your Pivot Notes
  • 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

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 4 Worksheet Examples

There are 5 main examples in Pivot Notes 8. Example1 is a straightforward single Pivot Table that uses Pivot Notes. Example2 (Multiple Tables) demonstrates using multiple Pivot Notes with multiple Pivot Tables. Example3 (Links, Tags & Val.) demonstrates using Hyperlinks, Conditional Formatting and Data Validation with Pivot Notes. Example4 (Protection) demonstrates using Pivot Notes with Worksheet Protection. Example5 (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. 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(s) you are using for your Notes. Include the word Index and then enter your Note Headers (required!). Style the Headers as you want. Style the next Row down as you want. Set the Column widths as you want. Set the Row Heights as you want

[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"


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

Support

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes

  • 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
...or Contact Mark Kubiszyn via our Facebook Page - 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 or via Messenger. 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

Bug Fixes

* There are currently no bugs identified

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

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