Giving feedback in an Excel worksheet UI

I am creating a tool as part of my work at the University of Sydney to extract data from Google Trends via die Google Extended Trends API for Health. Really an exciting project, and a tip of the hat to Google for making this awesome tool available.

Part of the project is getting multiple samples of the data from the Google servers (I am hoping to get a publication out soon about the why and how of this kind of sampling), and then aggregating them (just calculating data-point averages, really), and charting them. Since most of the output will end up in an Excel workbook, it just made sense to put the whole thing in Excel.

The first part of this is creating a UI with which the user can specify the extraction that needs to be done.

I have built a worksheet-UI, in similar fashion to that recommended in the Excel developer’s “bible”, Professional Excel Development. Part of the UI involves giving feedback when input is still required or inappropriate. These feedback messages can, of course, be given in different ways. Using VBA, a message box can tell the user what is needed, but the problem with that approach is that message boxes can be cancelled, and the input still not corrected. So it makes sense to display on-sheet messages which persist for as long as the issue exists, and these can easily be done using in-cells formulas. The question then becomes, where to put these error messages.

An early version (slightly condensed) of the UI looked like this:

Figure 1    Early version of UI error messages

The two salient points from this figure are firstly that there are error messages in several places (below the API_Key, although no error is currently displayed, below the Target, and in the Messages block). This can clutter up the UI very quickly, so perhaps it is best to put all error messages in one location, like the Messages block. But that brings me to the second point, which is that each cell in the messages block contains one error-checking formula. So in Figure 1, the first four error messages are not displayed (because there are no errors), and the fifth is. This just looks weird. Of course, I could write my error checking formulas to display some kind of “All’s well” message for each formula, but my general aim is to reduce clutter, so I prefer to have the blank cells than a succession of “All’s well” messages.

Figure 2 gives an idea of the structure of the worksheet, showing some of the error checking formulas. It can be seen that I have made extensive use of named ranges to simplify things, and that most of the formulas use a simple IF function with a blank string (“”) displayed if there is no error.

Figure 2    Error checking formulas in UI

So, I was a little dissatisfied with the look. If some issues are addressed and others not, then unsightly gaps are created between the various messages.

What I wanted was a list of messages, where the various issues are displayed top-down. That means that each cell in my list (range) of error messages must be able to display any of the error messages, or the list of error messages must “magically” rearrange themselves so that the displayed messages are at the top of the list.

This is how I went about doing it.

  1. The first thing to do was to create an off-area range where each message is displayed—off-area meaning a range of cells that will be hidden when the finalised version of the workbook is released. So I cut the original list of error messages and pasted them in cells $N$16:$N$23 (columns K to XFD will be hidden in the final version), as shown in Figure 3 (which actually shows the end result, but that is useful for understanding the process).

Figure 3    Moving the error checking formulas

  1. I added a single “All is well” message. One is fine, so long as it appears right at the top. In cell $N$24 I used this formula:
    {=IF(SUM(LEN(N16:N23))>0,””,”All inputs are completed. Data extraction can be commenced.”)}
    For the uninitiated, the braces {} indicate that this formula is an array-entered (or CSE) formula. You do not type the braces, but enter the formula and then press Ctrl+Shift+Enter (hence the CSE alias) to “transform” it from a regular formula to an array-entered formula. It essentially looks for zero-length strings in all the error message cells, and if so, returns the “All’s well” message shown.
  2. To figure out which of the error messages to show in the original message area, I added some formulas in columns L (with the heading “Index” in Figure 3) and M (with the heading “Display position” in Figure 3). The text descriptions in the “Refers to” column (Column O) are not used, and are just for my own reference, so that I can debug or modify the formulas later on.
    Column L has the formula =COUNTA($N$16:$N16) in cell L16, and so on down—i.e., L24’s formula is =COUNTA($N$16:$N24).
    Note that the first part is entirely absolute ($ signs before the column and row references), but that the second has a relative row reference. Thus, as it is copied down, the row changes. Essentially, it is counting the number of rows from the fixed starting point if $N$16, and using that as a means of “numbering” (or indexing) my error messages. Simple, really.
  3. Next is the magic bit. The formula in column M that tells Excel whether to display the error message or not, and if so, in what position. It again uses an absolute starting point, with a relative row end: =SUMPRODUCT(IF(LEN($N$16:$N16)>0,1,0)*(LEN($N16)>0))
    Essentially, what this formula does, is count the non-blank error messages. The first part, IF(LEN($N$16:$N16)>0,1,0), basically says, “If the cell length is longer than zero (i.e., not a zero-length string), then return a 1, but if its length is zero, return a zero). The second part, (LEN($N16)>0) returns True or False to the statement “length is greater than zero.” But True, in Excel, equals 1, and False, in Excel, equals 0. So when the position is multiplied by zero (i.e., when there is no error message), it returns zero. When the position is multiplied by 1 (there is an error message), then the position is returned. Perhaps not that simple, but it works. Figure 3 shows that the 1st, 3rd, 5th, 7th, and 8th error messages will be displayed in the 1st through 5th positions.
    the formulas (partly) are shown in Figure 4.

Figure 4    Error message index and display position formulas

  1. On the off chance that I will be adding more error messages, it makes sense to create a dynamic range name that encapsulates my list of error messages, with their indexes and positions. So I added a range name MessageBaseList with this formula:
    =’Query specification’!$L$16:INDEX(‘Query specification’!$O$16:$O$30,
    COUNTA(‘Query specification’!$N$16:$N$30))
    I somewhat arbitrarily used row 30 as the end point, as that is near the end of my visible interface, and I judged it unlikely that I would be adding more error messages than that!
  2. All that remains is to use a formula in my original message area to look up the error messages that have display positions (i.e., those values in column M greater than 0), and return them.
    Again, I use the absolute anchor with a relative end to allow the formula to adjust as it goes down:
    This formula essentially self-determines its position in the message display area using the COUNTA function, and then uses the INDEX function to check if there is an error message with the corresponding position, and if so, displays that. Again, perhaps not quite that simple, but it works!
    Note that the message area is essentially E16:E30, although I merged cells to get enough space to show the messages. Generally, I would advise against merging cells like this, but the layout and the fact that I had to make provision for up to 30 search terms (the limit allowed by the Google API) meant that I really had to make the best use of the space I had at my disposal. The solution works, so I will settle for that, but I do confess that it would be simpler if I had not merged cells.

The basic concept is that the error messages that are active will be displayed, from the top down, in the display area. I can finally add my single “All’s well” message to indicate when there are no more errors, which does help with feedback but still minimises clutter (Figure 5).

Figure 5    “All’s good” message

I will be publishing the complete tool when I am done, and I will update this blog post with a link when I do, so that you, dear reader, can see the effect in action!