To make your chart truly interactive, you can use one or more dynamic ranges in your chart and then use either a scrollbar or a drop-down list from the Forms toolbar to reveal the figures your readers want to peruse.
As you saw in the previous hack, you can use dynamic named ranges to add flexibility to your charts. But you also can use dynamic named ranges to create interfaces controlling which data the chart plots. By linking dynamic named ranges to custom controls, you enable users to change the chart data by using the control, which simultaneously will update the data in the worksheet or vice versa.
In this example, you will use a scrollbar to reveal monthly figures over a 12-month period. The scrollbar is used to alter the number of months reported. The scrollbar's value also is used in a dynamic range, which in turn is used as the data source of the chart.
To begin, set up some data similar to that shown in Figure 5-9.
Figure 5-9. Worksheet data for dynamic chart linked to scrollbar
Create a dynamic named range by
selecting Insert → Name → Define and call it
SALES_PERIOD. In the Refers To: box, type the
By using the
OFFSET function, you can use cell $C$5 to force the referenced range for SALES_PERIOD to expand both up and down as the ...