Microsoft Office Tutorials and References

In Depth Information

**Creating an Interactive Chart without VBA**

Figure 18-28:
The chart uses the data retrieved by formulas in A23:M24.

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 looks up data based on the contents of
City2
:

=VLOOKUP(City2,INDIRECT(DataTable),COLUMN(),FALSE)

After entering these formulas, I simply copied them across to the next 12 columns.

You may be wondering about the use of the COLUMN function for the third argument

of the VLOOKUP function. This function returns the column number of the cell that

contains the formula. This is a convenient way to avoid hard-coding the column to be

retrieved and allows the same formula to be used in each column.

Row 25 contains formulas that calculate the difference between the two cities for each month. I

used conditional formatting to apply a different color background for the largest difference and

the smallest difference.

The label above the month names is generated by a formula that refers to the
DataTable
cell

and constructs a descriptive title: The formula is:

=”Average “ & LEFT(DataTable,LEN(DataTable)-4)

Creating the interactive chart

After completing the previous tasks, the final step — creating the actual chart — is a breeze. The

line chart has two data series and uses the data in A22:M24. The chart title is linked to cell A21.

The data in A22:M24 changes, of course, whenever an
OptionButton
control is selected or a

new city is selected from either of the Data Validation lists.