Microsoft Office Tutorials and References

In Depth Information

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 con-

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

trieved 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 de-

scriptive title: The formula is

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

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 B21. The data in A23:M24

changes, of course, whenever an OptionButton control is selected or a new city is selected from either of the

Data Validation lists.