Microsoft Office Tutorials and References
In Depth Information
Creating the chart's data range
Following are the steps I used to create the drop-down lists:
1. Select J11:M11 (remember, these are merged cells).
2. Choose Data
Validation to display Excel’s Data Validation dialog box.
3. Select the Settings tab in the Data Validation dialog box.
4. In the Allow field, choose List.
5. In the Source field, enter =CityList .
6. Click OK.
7. Copy J11:M11 to J13:M13. This duplicates the Data Validation settings for
the second city.
Figure 7-17 shows the result.
Figure 7-17: Using the Data Validation
drop-down to select a city
Creating the chart’s 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 using formulas that utilize the
VLOOKUP function. Figure 7-18 shows the range of data that is used by the chart.
The formula in cell A23, which looks up data based on the contents of City1 , is
=VLOOKUP(City1,INDIRECT(DataTable),COLUMN(),FALSE)
The formula in cell A24 is the same, except that it is looking up data based on
the contents of City2 :
=VLOOKUP(City2,INDIRECT(DataTable),COLUMN(),FALSE)
Search JabSto ::




Custom Search