Dynamic Chart Presentation Solutions That Pack a Punch in Microsoft Office Excel 2007

As the chapter heading suggests, this section shows how to create solutions that have a powerful effect. In this regard, the mouse has some work to do, and so have you. Some of the examples presented here are visually elegant and sophisticated. In the preceding nine chapters, however, you have acquired knowledge and experience that will make it easier for you to understand such constructs and their formats. Consequently, the descriptions and notes in this chapter are on the brief side, but an occasional glance at the index will certainly help you. I also remind you to take a very close look at the models available on the CD-ROM and to examine them thoroughly (a little like CSI, but without the C!).

Consider the solutions presented here as additional, consolidated information about everything already written in relation to the options provided in Excel 2007, or simply as a refresher. To employ these options, you need certain skills and some creativity. Sure, it can be a painstaking effort to acquire the necessary skills for such an extensive software, especially because Excel 2007 is an exciting but also a demanding beauty in this regard. However, if you’re looking for ways to support and free your creative imagination, then Excel 2007 is a truly wonderful instrument that has found favor with many. So, let’s get started and see what unfolds!

Sorting? You can do without

With our first example, I wish to show you that the rS1.Method can also be completed with conditions more difficult than those outlined previously.

The Task and the Problem

The task is to create a solution that successfully represents a product’s quarterly sales figures, broken down into different years and cities. Monthly data is available for a total of 75 cities and we should be able to compare year with year and/or city with city.

The problem is that the necessary source data is delivered as monthly data that has been sorted in order of success. Therefore, the cities and their results are sorted in descending order, according to their sales figures. Consequently, the source data may display the cities in a different sequence each year. It is even more difficult to evaluate the data for the current year because the transferred data is sorted differently each month. However, this is necessary for other analyses and evaluations, and your orderer does not want this to change. Incidentally, this all concerns the following question, which quite often must be answered: how can we obtain consistent information from inconsistent data? Naturally, numerous database solutions can provide an adequate answer to this question. Within Excel alone, there are many different sophisticated variants, some of which may or may not require programming. However, this also includes a model that is secure in any application and easy for anyone to create, namely the model of a dynamic, non-programmed presentation solution. I will describe how this can work below. However, first take a look at the result.

The Focus 1 worksheet in the file 3Years.xlsx is designed for the Full screen presentation view and is protected without a password.

The upper right corner of the figure contains four ActiveX controls for making various different paired comparisons or cross-comparisons.

• For example, based on the information provided in Figure 10-1: what were the sales figures for Kansas City, KS in 2007 and how do they compare with 2008? (There is no column for Q4, 2008 because no data is currently available for this quarter.)

  

  Figure 10-1 Simple yet elegant, in a number of ways

• However, what were the sales figures for Kansas City, KS in 2007, and how do they compare with the sales figures for Worcester, MA in 2007? Incidentally, in this ActiveX control, you do not have to move down 39 positions from Kansas City: it is enough to select the current entry (by double-clicking it) and then overwrite it with the two letters "wo" (lowercase is sufficient). It couldn’t be easier. The chart is immediately updated to show the data from Worcester, MA.

• You can also determine the level of success achieved for product sales in Knoxville, TN in 2006 and how this compares with the result achieved in Oakland, CA in 2007. There are numerous scenarios that demand such comparisons, each of which seems somewhat strange at first glance. However, the text “Years AND Cities are unequal” below the chart draws your attention to a possible oversight in your query combination.

The above descriptions also clearly illustrate the following problem: a comparative query of 75 cities naturally makes it necessary to sort the city names in the control lists in alphabetical order. However, it is necessary here to extract data that is stored differently in three different data sheets; in other words, sorted according to values and on top of that sorted differently in each of the three sources.

Before we discuss how to solve such difficulties, let’s consider the working objective again.

• It must be possible to address one of three possible worksheets as a data source by simply clicking a year in a control (as mentioned previously, and explained in greater detail below, this can be achieved using the INDIRECT function).

• It must be possible to extract the quarterly data for a city from each source data sheet without knowing which of the 75 possible row positions is occupied by this city (this does not work when you use OFFSET or INDEX, except in a very roundabout way, but it does work with VLOOKUP).

To make it easier to understand the formula constructions for the chart basis, let me first provide an overview of the data structure and the control designs. I doubt you will be surprised to learn that, once again, there are redundancies. The model is, in many ways, designed to satisfy changing requirements and therefore can be adjusted without much effort, even to meet needs other than those outlined here. For the purpose of this exercise, you may wish to use the material available to supplement this solution.

Organization of the Source Data

The most important information about the Data 1 . . ., Data 2 . . ., and Data 3 . . . worksheets is provided below.

• All of the worksheets have the same structure, even if the data is sorted differently in each sheet. For fast and easy orientation, you will use different colors in the header area to differentiate between different types of data.

• The windows are fixed in both axes (View/Window/Freeze Panes) so that you can keep the labels in view even when the data range moves.

• Each data source has a node that corresponds to its sheet name: rD1.Node, rD2.Node, and rD3.Node. This is quite important. These range names differ in number only.

  

  Figure 10-2 Two auxiliary rows with different number sequences

• The monthly data presented here in the form of constants is summarized in columns L to P with formulas for quarterly data and annual data (therefore, it would be easy to use month-specific analyses to enhance this analysis). The presentation chart requires only the values in the range M12:P91.

• There are two horizontal auxiliary rows with number sequences. The upper row describes the distance from the node. The lower row indicates the column numbers in an array used by VLOOKUP formulas. Each of the three arrays in these worksheets has, in addition to the aforementioned nodes, another range name that corresponds to the sheet name: rD1.DataSource, rD2.DataSource, and rD3.DataSource. Once again, these range names differ in number only. Depending on their actual use, the arrays span the entire values range and therefore also contain monthly data not visualized here.

• In the Data 1 . . . worksheet, however, the cells that are retained for monthly data that does not yet exist but will in the future are filled with #N/A, which is usually the case with my models. Similarly, the relevant totals are also filled with #N/A and therefore invisible in the associated charts.

Controls with Text Output

The city names and years sorted in alphabetical order for use in the four model controls are defined in the Lists 1 worksheet.

Note the following in relation to the output values of the ActiveX controls: while the ComboBoxes for selecting the year output their index value in numbers from 0 to 2, as was previously determined and accepted (and can’t be anything else for form controls), the ComboBoxes for selecting the city behave differently; they do not write a number. Instead, they write the text selected by the user to its LinkedCell. Excellent, but why? Because whoever has such text also has a search term that can be used in VLOOKUP (and in other functions).

How can this be set up? It’s really quite easy: the BoundColumn property of the ComboBox must not be set to 0 (as was previously demanded), but to its default value of 1.

With regard to the ComboBox standards, the MatchEntry property makes it easy for users to control text entries within extensive lists, simply by entering the first letters. Selecting fmMatchEntryComplete triggers extended matching; that is, as soon as one or more letters are entered in the control (by selecting and overwriting existing entries), the object searches its ListFillRange for an entry that begins with the letter(s) entered and proposes it for selection.

Formulas of the Chart Basis

The chart data is compiled in the Basis 1 worksheet. This sheet does not require copies of the four controls for the model to work. However, these copies provide you with a better overview when testing and checking the formulas.

Note the following points with reference to Figure 10-4:

K11 ="rD"&rL1.Year01Sel+1&".Node"

When you use the output value of a control, this formula (and similarly the formula in K12) composes text, namely the name of the node that belongs to the selected year. If you click 2008, that is, the first entry in the ComboBox, Excel generates the text rD1.Node. If you click 2006—the third entry—Excel generates the text rD3.Node. You now have a piece of information that you can use as a reference argument, in connection with INDIRECT, in many different formulas (for example, in OFFSET).

This describes an important key element of the rS1.Method that influenced my choice of naming conventions:

• Clicking a control generates a number.

• A formula uses this number to generate a specific range name (generally a node). The number alone is the variable element of the range name.

• The range name used as a variable in this way is the reference argument for other formulas.

In other words, you can click a control to determine the worksheet or cell range from which Excel should read the data that you’re interested in.

This formation of variable node names is particularly useful in rS1 models, but is nevertheless not required here; both formulas potentially enhance the model’s functions.

The formulas in L11:L12, on the other hand, will be used here immediately. The same principle provides the same benefits:

L12 ="rD"&rL1.Year02Sel+1&".DataSource"

Both cells therefore contain the optional text rD1.DataSource, rD2.DataSource, or rD3.DataSource. Make sure that cell L12 has rL1.Year02Sel instead of rL1.Year01Sel as in L11. However, both formulas are identical in every other regard. The same is true of the formulas in K11:K12.

N11 =INDEX(rL1.Year01List,rL1.Year01Sel+1,1)&" "

Similar cell: N12. These formulas determine the selected year, which is used as the first part of the relevant legend label in the chart. I used &" " to insert a space after the formula because I felt that the gap between the year and city name in the legend was insufficient.

O11 =rL1.City01Sel

Similar in cell O12: the second string for the relevant legend label in the chart and the search term for the VLOOKUP formulas. Because the relevant ComboBox outputs text (as shown above), this can be read directly from the control’s LinkedCell.

P11:T12,

for example P11 =VLOOKUP($O11,INDIRECT($L11),P$5,FALSE)

Here is the gist of the matter: “determine a value that is to be localized as follows: use the search term provided in cell O11. Find this string in the first column of the array whose name is in cell L11 (and was created there as a variable for the use of INDIRECT). Once you find it, take the value in this row from the column in the array whose column number is provided in cell P5.”

In our example, the notation =VLOOKUP("Knoxville, TN",rD2.DataSource,3,FALSE) would be a static equivalent of the dynamic formula. This is just one of 240 access options that can be controlled with two mouse clicks. This is the case for three source-data ranges. For other requests, however, there could be 10, 50, or more source-data ranges. That makes absolutely no difference when constructing such a chart basis.

The two formulas in column T are “reserves” that are not required here.

The data range N10:S12 (colored pale red in the worksheet) is the data source for the chart that presents the collated custom data in the Focus 1 sheet.

Specials in the Focus Worksheet

There are some special features here, but you can only explore these formatting considerations after you unprotect the sheet. Note the following three points:

• The “dollar sign” image is stored on the CD-ROM under \Materials\Pictures\Dollar.tif

• It is interesting that there seems to be a smooth transition between the chart area and the image. Also, take a look at how the chart border has been formatted.

• The plot area is designed with the 3-D option Soft Round. This gives you a very slightly three-dimensional depth, like a framed picture. However, there is just one visually disappointing disadvantage associated with using column charts: the columns are generally too far “forward,” practically on the “outer” edge of the mock border instead of being “set back” on the lower edge of the “picture area.” In this case, a little trick rectified the problem. As is frequently the case, two very important components of chart design play a role here: axis scaling and number formatting.

It only remains for me to mention the formula that outputs the aforementioned alert:

J29 =IF(
    AND(rL1.Year01Sel<>rL1.Year02Sel,rL1.City01Sel<>rL1.City02Sel),
    "Years AND Cities are unequal","")