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).

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.

Note