Excel: adding interactive drill down to a 'Last Ten Results' indicator panel

A previous article outlined a simple method for creating a 'Last Ten Results' indicator panel in Excel (see link). This supplement shows how to add interactive drill down functionality (Excel skill level: advanced)

Another previous article had shown how to use Excel comments boxes dynamically to provide drill down details (see link). This method can be adapted to work with the last ten results indicator panel. There are a few technical challenges.

Reversing a linking formula
The cells coloured green,  red and grey in the indicator panel read values from a separate data area. The way the data is structured (in this example) the drill-down needs to read from three adjacent rows in the data area, e.g. cells AF10, AF11, AF12.  This is trickier than it first seems. 

Some VBA code which can do this set out below. It is triggered by the Worksheet Selection Change event i.e.the code is run each time a different area on the worksheet is selected. So the code is run when any of the coloured bars are clicked on, displaying the appropriate details for the cell It is run again when any non bar cell is clicked on, removing the previously displayed details.

To make the code a little easier to follow it has been split into three blocks. The first block defines the memory variables that will be needed, clears any pre-existing comments from the worksheet, tests to see if the selected areas is withing the defined target area (i.e the whole indicator panel), and tests to see if it is a coloured results cell or simply the margins around the coloured results cell (requiring no drill down) 

The next block of code records the coordinates of the cell where we want the drill down to be displayed and works out the coordinates of the topmost data cell relating to it

The final block collates the details from the data area into a cell comment and makes it visible. The details are produced by concatenating all the individual pieces, and using Chr(10) to force new lines within the comments box.

Inevitably, this VBA code could be written in a more compact and slick manner. But it works as is and runs quickly enough. Also note that the method of 'reversing' the link to identify the data cell is designed for cases where both sections are on the same worksheet. It would need further refinement if the data was located remotely


References

Excel: creating a 'Last Ten Results' indicator panel without using VBA  by Edgar Bolton. 
B I Dashboard Design. 31/01/2016  (link)

Excel: Using cell comments to provide "drill-down" details by Edgar Bolton
B I Dashboard Design. 05/08/2015   (link)

Excel: Using arrows to indicate direction of change in performance  by Edgar Bolton
B I Dashboard Design. 26/07/2015   (link)

How to convert Excel column numbers into alphabetical characters. Microsoft. Article ID: 833402 - Last Review: 09/19/2011 00:01:00 - Revision: 5.0   (link)

Excel: Using cell comments to provide "drill-down" details

A previous article (link) adapted a sample panel from the BBC website to look at options for creating arrows in Excel to indicate direction of change:

The article covered different ways of creating arrows in Excel and the need to define tolerances to create thresholds for changing arrows. The example Excel rendering introduced additional columns to hold both the previous values and the tolerances. These would be hidden in a real, deployed dashboard.

Some Design Considerations

In terms of dashboard design, the BBC-style panel could be simplified. There is a hierarchy within the information. From most the important downwards, something like:

• Markets  i.e. what is the whole panel about
• Each Index and whether up or down (the most important information)
• The latest value of each index (it could be argued that some people would find % change more important than the actual value) 
• The % change and/or previous value of each index (in conjunction with present value, these are two versions of the same thing, although % change conveys more information quickly)
• Full details of the activity of each index over a period of time  (a 'drill down' to the full details for each index can be provided in Excel - as well as on a website - as a hyperlink to a second page of details). 

Re-arranging the panel to reflect the hierarchy:

This simplified design makes the most important information readable at a glance and also relegates the calculated % change data from the panel. 

Cell Comments as a method for providing drill-down

One way to retain access to details from within the panel (without the need to jump to a different page via a hyperlink) is to use cell comments:

Comments can be added to cells manually but this would be extremely time consuming to set up and maintain, and prone to error. To be of real use, the comments need to be generated automatically.

The Worksheet SelectionChange event provides a method for triggering updates. The example above uses the  .AddComment method in VBA to create the comments box itself. The text within the comments box needs to be formatted to control the number of decimal places. Separate lines within the box are created using 'Chr(10)' in the text string.

Cells with a comment are designated in Excel by a small red triangle in the top right hand corner. Microsoft appears to determined to make this feature unalterable. It cannot be adjusted through options, property settings or by using VBA. Workarounds are discussed variously on Excel forums: all seem to rely on creating a differently coloured triangle over the top of the red one. The approach taken here, to avoid having a dashboard covered in unsightly little red triangles, is to wait until the comments are needed before creating them and then delete them again afterwards, straight away.

The VBA code used to generate the example above is as follows:

The details are collated from hidden columns:

• Column G: contains the percentage change
• Column H: contains the previous value
• Column K: contains a brief description of what the index is (for illustrative purposes)

The .visible property is set to true in order to open up the comments box.

Clicking on another cell within the target range in column E will trigger a three stage update:

• The last generated  comment will be deleted
• A new comment box will be constructed with the requisite information collated from the values for the same row in Columns G,H and K. 
• The new box will be opened up (made Visible)

If a cell outside of the target range in column E is clicked on, the last generated  comment will be deleted but no new comment box will be created. All comments will have been removed and there will be no red triangles left behind.

The approach has some limitations: the size and style of the comments box is hard to control (it is not possible to set 'autosize' to true on creation); selecting more than one cell can cause strange results.


Other options

An alternative to using cell comments is to create a text box. This works in a similar way to cell comments. It introduces additional technical challenges, most notably the need to determine the positioning of the box.



References

The VBA used here is a combination of ideas gleaned from various Excel user forums and some original experimentation. Because a lot of material recirculates in forums, it is difficult to trace original ideas reliably back to their creators to give due credit. The following are just a few of those referred to:

Shapes.AddTextbox Method (Excel). Excel 2013 Developer Reference. Microsoft. https://msdn.microsoft.com/en-us/library/office/ff838832.aspx

VBA Tips - Find Out If A Cell Is Within A Range. ExcelExperts.com.21 May, 2009. http://excelexperts.com/VBA-Tips-Find-Out-If-A-Cell-Is-Within-A-Range

Using the Intersect Method in Excel VBA. 

http://www.ozgrid.com/VBA/vba-intersect.htm

The Complete Guide to Ranges and Cells in Excel VBA by Paul Kelly.2nd January 2015. Excel Macro Mastery. 

http://excelmacromastery.com/Blog/?p=165

How to change comment indicator color in Excel?. ExtendOffice.

http://www.extendoffice.com/documents/excel/2253-excel-change-comment-indicator-color.html