Excel Video 92 continues our discussion about using OFFSET to make interactive charts. There are two things I’d like to show you in this video. First, notice how that when I change cell G14, the cell I named Include, the title of the chart changes to reflect the correct number of months. To make this easier to see, I made cell H14 visible, but once your chart title is updating properly, you can easily hide this cell. In H14 I put the text that I wanted in quotes (including spaces as necessary), followed by the & sign to concatenate the Include cell, followed by another & sign, followed by the rest of the text in quotes. Using the & sign allows me to include the information in Include (G14) as part of my cell. Then all I have to do is to make the Chart Title =H14, and my title updates automatically.
The second trick is how I use OFFSET to define LastXDate, which is the date range for the x axis of my chart. Note how I base LastXDate on LastX, but my column variable is -2. That means that my range starts where LastX starts, but the -2 moves the range two columns to the left, where my date range starts. The height of my date range is the same height as my data, which is the number of months to include. The width of my date range is 2, so I pick up both the month in column A and the year in column B. Many times practice management software sends the month in one column and the year in another column when sending data to Excel. We could always concatenate the month and year into one new column using the & command, but we don’t have to. All we need to do is make the date range for the x axis 2 cells wide and Excel takes care of the rest.
Our chart’s now ready for some interactive tricks that look professional but don’t require any programming. I’ll demonstrate the first trick in the next video.
