Peter Bartholomew, March 2017
Dynamic Buttons & Shapes
Sometimes something more than text is called for, if you are to get the attention of your users. This post suggests a way of introducing a button that colours or greys-out according to the situation.
To do this, versions of the button image are placed over the named ranges 'sortButton.on' and 'sortButton.off' (right-hand sheet). The selection is made by using a Named formula that returns one or other of the ranges, that is 'sortButton' that refers to
= IF( sorted?, sortButton.off, sortButton.on )
The active picture on the left is then linked to the correct image by typing '=sortButton' into the Formula Bar with the shape selected. The idea is to remind the user to Sort the data Entry field manually or, if Macros are enabled, to click the newly activated button.
I will follow up by describing the array-formula that determines whether the data is sorted or not and switches between exact search and approximate search accordingly.
For many, it is the use of shapes or clipart that I think may be of greater interest.
= IF( sorted?, sortButton.off, sortButton.on )
The active picture on the left is then linked to the correct image by typing '=sortButton' into the Formula Bar with the shape selected. The idea is to remind the user to Sort the data Entry field manually or, if Macros are enabled, to click the newly activated button.
I will follow up by describing the array-formula that determines whether the data is sorted or not and switches between exact search and approximate search accordingly.
For many, it is the use of shapes or clipart that I think may be of greater interest.
Is The Range Sorted?
To determine this requires (n-1) comparisons to be made and aggregated. Not only that but it needs to be dynamic in order to adjust as the number of entries in the table is changed. One could use a helper field in the table and filter out the first or last term as appropriate. An alternative is to use Named formulas.
First, for readability, a slightly more compact name 'entry' is used for the Structured Reference by defining it to refer to
= Table1[Entry]
This has 'n' rows given by
= ROWS(entry)
The first (n-1) terms are defined to be the array 'previous' that refers to
= INDEX(entry, 1) : INDEX(entry, n-1)
and the array 'next' (n-1) is
= INDEX(entry, 2) : INDEX(entry, n)
The Boolean array 'Ascending?' is then given by
= (previous <= next)
Although the Name 'Sorted?' is based upon an array calculation, the final aggregation within a worksheet formula
= AND( Ascending? )
does not need to be entered as an array formula; the named formula has already achieved that.
First, for readability, a slightly more compact name 'entry' is used for the Structured Reference by defining it to refer to
= Table1[Entry]
This has 'n' rows given by
= ROWS(entry)
The first (n-1) terms are defined to be the array 'previous' that refers to
= INDEX(entry, 1) : INDEX(entry, n-1)
and the array 'next' (n-1) is
= INDEX(entry, 2) : INDEX(entry, n)
The Boolean array 'Ascending?' is then given by
= (previous <= next)
Although the Name 'Sorted?' is based upon an array calculation, the final aggregation within a worksheet formula
= AND( Ascending? )
does not need to be entered as an array formula; the named formula has already achieved that.
So How Is The Boolean 'Sorted?' Used
The Boolean value of ‘Sorted?’ controls the graphics, the main point of this discussion. If Macros are used to initiate sorting the data table, the value of 'Sorted?' can also be read and used to determine whether action is required or whether the Shape's 'on action' macro returns immediately.
The reason that sorting is important is that VLOOKUP and the more straightforward MATCH each implement two very different search strategies. The simple but robust ‘exact’ search examines every value in the list until it finds a match. The alternative ‘approximate’ approach implements a binary-search. This is very efficient but relies upon the data being sorted if it is to identify the correct location within the search range. The catch here is that both VLOOKUP and MATCH return the values at that location irrespective of whether the search string is actually present. In the present work INDEX/MATCH has been selected in preference to VLOOKUP so that the process of checking the validity of the match and returning the required value can be achieve with one search rather than two.
The value of ‘sorted?’ therefore has a key role as the third parameter in the MATCH that forms the first step of the lookup. The formula,
= MATCH( required, entry, sorted? ),
searches the first column of the table for the 'required' user input. If the table is sorted, the parameter ensures an approximate search is used (requiring 10 comparisons for the illustrative example in the worksheet); if it is not sorted the more computationally intensive exact search is used (here requiring up to 1024 comparisons). By enclosing the formula within IFERROR, i.e.
= IFERROR( MATCH( required, entry, sorted? ), "" )
a blank field is returned whenever the MATCH fails. Because an approximate match will have returned the location where the 'required' string would appear, irrespective of whether it is actually present, the next check is to return the content of the 'Entry' field an compare it with the 'required' user input. Hence 'found?’, given by
= IF( idx<>"", INDEX( entry, idx ) = required ),
determines whether the corresponding value should be returned from the Table's 'Value' field using
= IF( found?, INDEX( Table1[Value], idx ), "" )
The reason that sorting is important is that VLOOKUP and the more straightforward MATCH each implement two very different search strategies. The simple but robust ‘exact’ search examines every value in the list until it finds a match. The alternative ‘approximate’ approach implements a binary-search. This is very efficient but relies upon the data being sorted if it is to identify the correct location within the search range. The catch here is that both VLOOKUP and MATCH return the values at that location irrespective of whether the search string is actually present. In the present work INDEX/MATCH has been selected in preference to VLOOKUP so that the process of checking the validity of the match and returning the required value can be achieve with one search rather than two.
The value of ‘sorted?’ therefore has a key role as the third parameter in the MATCH that forms the first step of the lookup. The formula,
= MATCH( required, entry, sorted? ),
searches the first column of the table for the 'required' user input. If the table is sorted, the parameter ensures an approximate search is used (requiring 10 comparisons for the illustrative example in the worksheet); if it is not sorted the more computationally intensive exact search is used (here requiring up to 1024 comparisons). By enclosing the formula within IFERROR, i.e.
= IFERROR( MATCH( required, entry, sorted? ), "" )
a blank field is returned whenever the MATCH fails. Because an approximate match will have returned the location where the 'required' string would appear, irrespective of whether it is actually present, the next check is to return the content of the 'Entry' field an compare it with the 'required' user input. Hence 'found?’, given by
= IF( idx<>"", INDEX( entry, idx ) = required ),
determines whether the corresponding value should be returned from the Table's 'Value' field using
= IF( found?, INDEX( Table1[Value], idx ), "" )
Activating The ‘Sort’ Button
As suggested above, a further use of the worksheet Name 'sorted?' is to control the action of a macro. If you want to be able to re-sort the list simply by clicking the button when it is blue then this is possible by assigning the following macro to it:
Option Explicit Sub ListSort() If Not [sorted?] Then Application.ScreenUpdating = False With [Table1].ListObject .Sort.SortFields.Clear .Sort.SortFields.Add _ Key:=Range("Table1[Entry]"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal With .Sort .Header = xlYes .Orientation = xlTopToBottom .Apply End With End With Application.ScreenUpdating = False End If End Sub
The point is that the line
If Not [sorted?] Then
reads the value of the cell or Named formula and exits the sub if it is 'True', without affecting one’s ability to use Undo; otherwise the list is sorted once more. An alternative syntax is
If Not Evaluate("sorted?") Then
Keeping Up Appearances
If you download the workbook or even from the picture above you will most likely see that the workbook simply does not look like most others. Why?
The strategy of the workbook is to reference data using Structured References or absolute Named ranges. That means that of the 17 billion or so cells on a worksheet only a couple of thousand are addressable in this workbook.
I use a modified version of the 'Normal' Style to grey out every cell on the worksheet to show it as unusable (except for annotation and unreferenced 'shopping lists'). Then to introduce actual data, I reformat the range to 'Input' style and apply its Name (or Ctrl/T for a Table). I could leave the style as Microsoft’s 'ginger' but I have chosen to modify that to something more typical of the familiar worksheet cells since they are now usable as cells.
What I have done is to define my address space and dimension the arrays. Those of you familiar with VBA will see an attempt to reproduce the discipline of
Option Explicit
Dim myArray(1024, 2) as String
into the worksheet environment. I actually want mistyped references to produce #NAME? errors rather than leaving it to the auditor to play 'hunt the needle in the haystack'. I welcome #REF! errors; they are better than going beyond the bounds of a range without realising it.
The only other thing of note is the fact that the sheet Headings are hidden from View. The reason is that I never use coordinates to define references and I simply do not care where the data may appear on the worksheet. As long as data objects are labelled, distinct and separated by white space, I am in with a good chance of finding them.
Also, since the meaning of formulas based on names is self-evident, I have no need to follow precedent links to determine the significance of the references.
Please let me know your thoughts in the comments section!
The strategy of the workbook is to reference data using Structured References or absolute Named ranges. That means that of the 17 billion or so cells on a worksheet only a couple of thousand are addressable in this workbook.
I use a modified version of the 'Normal' Style to grey out every cell on the worksheet to show it as unusable (except for annotation and unreferenced 'shopping lists'). Then to introduce actual data, I reformat the range to 'Input' style and apply its Name (or Ctrl/T for a Table). I could leave the style as Microsoft’s 'ginger' but I have chosen to modify that to something more typical of the familiar worksheet cells since they are now usable as cells.
What I have done is to define my address space and dimension the arrays. Those of you familiar with VBA will see an attempt to reproduce the discipline of
Option Explicit
Dim myArray(1024, 2) as String
into the worksheet environment. I actually want mistyped references to produce #NAME? errors rather than leaving it to the auditor to play 'hunt the needle in the haystack'. I welcome #REF! errors; they are better than going beyond the bounds of a range without realising it.
The only other thing of note is the fact that the sheet Headings are hidden from View. The reason is that I never use coordinates to define references and I simply do not care where the data may appear on the worksheet. As long as data objects are labelled, distinct and separated by white space, I am in with a good chance of finding them.
Also, since the meaning of formulas based on names is self-evident, I have no need to follow precedent links to determine the significance of the references.
Please let me know your thoughts in the comments section!
Download
Demo workbook for Excel 2007 or later
