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
The formula in cell A24 is the same except that it looks up data based on the contents of City2 :
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.