Excel Video 89 takes our discussion of automatically updating charts using OFFSET three steps further. We’ll start by looking at the chart from Excel Video 88. Note that the numbers in row 2 are set up with OFFSET so as we add more data, the numbers part of the chart updates automatically. The problem is that even if you add more years in row 1, the axis range doesn’t automatically update for you. The trick is to use OFFSET with the horizontal axis series as well. By having both the chart data series and the axis data series defined with names that use OFFSET, your entire chart can update automatically.
The second trick in Excel Video 89 is to use the COUNTA function to count non-blank cells. The COUNT function we used earlier works fine for numeric data, but COUNT won’t count cells with text. To make the Year series work in this chart, I used COUNTA to count all of the non-blank cells in row 1. Now the OFFSET formula works for the years as well as the data.
The third trick is to set up one name with OFFSET (SanDiego in Excel Video 89) and then base other data series in the chart on that series. You’ll see LosAngeles is defined as =OFFSET(‘3Examples’!SanDiego,1,0), which tells Excel to start with the SanDiego series as the reference, go down 1 row, and call that series LosAngeles. As long as San Diego and Los Angeles have the same number of data points, this trick works great. Once you have one name set up, it’s easy to add multiple names for multiple data series just by moving the OFFSET coordinates.
Excel Video 89 turned out longer than normal, but there’s still more in this worksheet worth discussing. In Excel Video 90, I’ll show you how I made the drop-down box show the detail of Year, SanDiego, and LosAngeles without using macros or any code.
