Microsoft Office Tutorials and References
In Depth Information
Creating an Interactive Chart without VBA
To make working with the list of cities easier, I created a named range, CityList , which refers
to the first column in the PrecipitationData table.
Following are the steps that I used to create the drop-down lists:
(Remember, these are merged cells.)
Choose Data➜Data Validation to display Excel’s Data Validation dialog box.
Select the Settings tab in the Data Validation dialog box.
In the Allow field, choose List.
In the Source field, enter =CityList .
Copy J11:M11 to J13:M13.
This duplicates the Data Validation settings for the second city.
Figure 18-27 shows the result.
Figure 18-27: Use the Data Validation drop-down list to select a city.
Creating the interactive chart data range
The key to this application is that the chart uses data in a specific range. The data in this range is
retrieved from the appropriate data table by using formulas that utilize the VLOOKUP function
(see Figure 18-28).