To create the first range name, select the range B3:B10; choose Insert, Name, Define; enter the name StatData; then choose OK. Take the same approach for the other two names.
(You also can choose Ctrl+F3 to launch the Define Name dialog.)
Format the rows and columns as shown in the figure, then enter the data shown above. Here are some things to watch out for:
The Control Worksheet
The Control worksheet is very simple in this case. Cell B2 contains the only value that you will update monthly. In the figure, it contains the value 1/1/2007.
Cell B5 contains a formula that returns the column number within the database for the value entered for the CurMonth. Enter the formula for the cell shown:
This formula finds the CurMonth value in the range named Dates (as explained in the previous section) and returns the column number for that date. The zero in the last argument tells Excel to find an exact match.
Because the formulas in the figures below all refer to the ColNum range, the figures automatically update when you change the CurMonth date in cell B2 above.
The Horizontal Figure
Here is the figure shown at the beginning of this article, with the row and column headings included:
I used the techniques described in Display Any Colors in Excel to format the colors for this figure. But for now, rather than dark green and tan, let's suppose you want to use blue and gray for your version of this figure:
1. Add a new worksheet to your workbook.
2. Select cell B2 and assign a blue color pattern. To do so, use the Fill Color tool on your Formatting Toolbar or choose Format, Cells, Patterns, and then choose the blue color.
3. Enter the text "stat1_" in cell B2. (You also could enter "Stat1_" because the case doesn't matter.) Then format the cell's font as blue. To do so, use the Font Color tool or choose Format, Cells, Font, and then choose the blue color. Doing so makes the text invisible, which is what we want. (You'll see how we use this text shortly.)
4. Select the range C2:E2 and assign a black color pattern. Enter "KEY STAT" in cell C2 and assign a white font to that cell. Format the font as bold. The example uses the Cambria 14 font, but you can use any font you want.
5. Select the range B3:D7. This range needs to be merged into one large cell to contain the large number. To do so, choose Format, Cells. In the Alignment tab, check Merge Cells, then choose OK.
6. The font in this cell is Cambria 40, Bold. You can use any font you want.
7. Select the range E3:I3. Merge this range into a large cell.
8. The font in this range is Calibri 14, Bold. Apply any font settings you want.
9. Select the range E4:I7. Merge this range as well.
10. The font for this range in the figure is Calibri 11.
11. Each merged range uses a formula to return data from the database. Enter these formulas for the ranges shown:
In cell B3, the reference to cell B2 returns "stat1_". When combined with "Data", the MATCH function searches for "stat1_Data" in the StatData range, and returns that label's row index number. The INDEX function returns the value in Data for that row, and for the appropriate column number. The other two formulas work similarly.
The Vertical Figure
This figure presents the data using a vertical format. I created it using a new worksheet in the same workbook, but you could create it in the worksheet with the first figure.
Use the same approach to create this figure as the previous one. Here, however, enter the text "Stat2_" in cell B2.
Displaying the Figure
Whether you use the horizontal or vertical versions of these figures, they look best when their column widths are set carefully. Therefore, it's probably best if you create each figure in its own worksheet and then use Excel's Camera Tool to display them in a report sheet. The article Add Class to Your Reports With Excel Drawing Objects explains how to use this tool.