Microsoft Office Tutorials and References
In Depth Information
Using the Button Control
Next, you create the analysis layer (staging table) that consists of all formulas, as shown in Figure
12-10. The idea is that the chart actually reads from this data, not the raw data. This way, you can
control what the chart sees.
Figure 12-10: Create a staging table that will feed the chart. The values of this data are all formulas.
As you can see in Figure 12-10, the formulas for the 2012 row simply reference the cells in the raw
data for each respective month. You do that because you want the 2012 data to show at all times.
For the 2011 row, you test the value of cell A12 (the cell that contains the output from the check box).
If A12 reads True, you reference the respective 2011 cell in the raw data. If A12 doesn’t read True, the
formula uses Excel’s NA() function to return an #N/A error. Excel charts can’t read a cell with the #N/A
error. Therefore, they simply don’t show the data series for any cell that contains #N/A. This is ideal
when you don’t want a data series to be shown at all.
Notice that the formula shown in Figure 12-10 uses an absolute reference with cell A12.
That is, the reference to cell A12 in the formula is prefixed with a $ sign ($A12). This
ensures that the column references in the formulas don’t shift when they’re copied
Figure 12-11 illustrates the two scenarios in action in the staging tables. In the scenario shown at the
bottom of Figure 12-11, cell A12 is True, so the staging table actually brings in 2011 data. In the
scenario shown at the top of Figure 12-11, cell A12 is False, so the staging table returns #N/A for 2011.
Figure 12-11: When cell A12 reads True, 2011 data is displayed; when it reads False, the 2011 row shows only