Microsoft Office Tutorials and References
In Depth Information
Using the List Box Control
In the example shown in Figure 12-26, the data that will be returned with this CHOOSE formula is
41767. Why? Because cell P2 contains the number 3, and the third cell reference within the CHOOSE
formula is cell B9.
Figure 12-26: Use the CHOOSE function to capture the correct data corresponding to the selected region.
You entered the same type of CHOOSE formula into the Jan column and then copied it across (see
Figure 12-27: Create similar CHOOSE formulas for each row/category of data and then copy the choose
formulas across months.
To test that your formulas are working, change the value of cell P2 manually, entering , , , , or . 5
When the formulas work, all that’s left to do is create the charts using the staging table.
If Excel functions like CHOOSE or INDEX are a bit intimidating for you, don’t worry. There
are literally hundreds of ways to use various combinations of form controls and Excel
functions to achieve interactive reporting. The examples given in this chapter are
designed to give you a sense of how you can incorporate form controls into your
dashboards and reports. There are no set rules on which form controls or Excel functions you
need to use in your model.
Start with basic improvements to your dashboard, using controls and formulas you’re
comfortable with. Then gradually try to introduce some of the more complex controls
and functions. With a little imagination and creativity, you can take the basics found in
this chapter and customize your own dynamic dashboards.