Microsoft Office Tutorials and References
In Depth Information
Using the Combo Box Control
Figure 12-19: Start with the raw dataset and a cell where the option buttons can output their values.
You then create the analysis layer (the staging table) that consists of all formulas, as shown in Figure
12-20. The idea is that the chart reads from this staging table, allowing you to control what the chart
sees. The first cell of the staging table contains the following INDEX formula:
Figure 12-20: Create a staging table that uses the INDEX function to extract the appropriate data from the raw
The INDEX function converts an index number to a value that can be recognized. An INDEX function
requires two arguments in order to work properly. The first argument is the range of the list you’re
working with. The second argument is the index number.
In this example, you’re using the index number from the combo box (in cell M7) and extracting the
value from the appropriate range (2012 data in P7:P14). Again, notice the use of the absolute $ signs.
This ensures that the cell references in the formulas don’t shift when they’re copied down and across.
Take another look at Figure 12-20 to see what’s happening. The INDEX formula in cell P2 points to
the range that contains the 2012 data. It then captures the index number in cell M7 (which traps the
output value of the combo box). The index number happens to be 7. So the formula in cell P2 will
extract the 7th value from the 2012 data range.
When you copy the formula across, Excel adjusts the formula to extract the seventh value from each
year’s data range.