Microsoft Office Tutorials and References
In Depth Information
Using the List Box Control
Figure 12-24: Start with the raw datasets that contain one line per region.
You then add a list box that outputs the index number of the selected item to cell P2 (see Figure 12-25).
Figure 12-25: Add a list box and note the cell where the output value will be placed.
Next, you create a staging table that will consist of all formulas. In this staging table, you use the
Excel’s CHOOSE function to select the correct value from the raw data tables based on the selected
In Excel, the Choose function returns a value from a specified list of values based on a
specified position number. For instance, the formula CHOOSE(3,”Red”, “Yellow”,
“Green”, “Blue”) returns Green because Green is the third item in the list of values. The
formula CHOOSE(1, “Red”, “Yellow”, “Green”, “Blue”) returns Red. See Chapter 11 to get
a detailed look at the CHOOSE function.
As you can see in Figure 12-26, the CHOOSE formula retrieves the target position number from Cell
P2 (the cell where the list box outputs the index number of the selected item) and then matches that
position number to the list of cell references given. The cell references come directly from the raw