Tuesday, August 3, 2010

Dynamic Named Ranges

Hi All,

I see a lot of time people ask that they cannot find the last row of the data. One of the most complicated scenario would be, to assign the dynamic source range to a chart. Suppose I have a chart and the number of rows in my chart would keep on changing time to time, so how would I manage this? One option is to use VBA code and assign a new source data whenever it is refreshed. But do we really need code behind to resolve this problem? I would say no :)

Before going further, I would like to tell you about Offset function, which i will be using to explain the Dynamic named Ranges.

Syntax of Offset function is :

OFFSET(reference,rows,cols,height,width)

Offset, in itself is a powerful function to design a dynamic range.

Parameter 1, Reference : This would be the starting point of your range. Offset function requires you to provide it a starting point, on which you will finally decide what range to be consider.

Parameter 2, Rows : This number is used to shift the starting point. Suppose if the Starting Point is A1, and the number of Rows (second parameter) is 2, then the starting point will be shifted by 2 rows and will point to A3.

Parameter 3, Cols : This number is used to shift the starting point by columns. Suppose if the Starting Point is A1, and the number of columns (third parameter) is 5, then the starting point will be shifted by 5 Columns and will point to F1.

Parameter 4, Height : This would represent the number of cells in a column, aka, the height of range. This can be assumed as the height dimension of a matrix.

Parameter 5, Width : This would represent the width of the range.

Examples :

Offset($A$1,1,1,5,5) : The range is $B$2:$F$6.

Explanation : Starting point is $A$1. Second Parameter, shift row by 1 so It will point to $A$2. As per third Parameter, shift col by 1 so it will point for $B$2. Forth parameter says that the Height of the required range should be 5 cells. So it will be : $B$2:$B$6. The fifth and last parameter says that the width of the range should be 5 columns. So the final range would turn out to be $B$2:$F$6.

Dynamic ranges can be very handy and useful in creating charts when the source range has to be dynamic and is of changing nature. For example, suppose if we have the following chart :



You can see that we have a very simple chart, with the source data as $A$3:$B$11. However, the data can be of a changing nature. For example, see the following screenshot...the data is increased by 5 rows and the new source should be $A$3:$B$16.



Rather than assigning the source data to chart again and again, we can create a Dynamic Named Range which can be flexible if the rows of our data are increased. Dynamic Named Ranges are nothing but a Name Range, with Offset Formula in its target range.

The chart earlier had the series as $A$3:$B$11, if you see and edit the source data of chart, you will see two ranges. One is the Range for Series :- $B$4:$B$11 and X Series which is dates :- $A$4:$A$11.

To create this, I would first create a X Range, which will point to the Date Column of our chart. It will certainly involve the Offset function. To get the dynamic range for Dates, I will start my Offset from $A$4, which is the start point. Setting up next two parameters as 0, 0 so that the starting point should not be changed. Since we have to calculate the rows on run time, so I will use CountA formula so that I can get the exact height of the data. CountA function returns the number of non blank cells in the specified range. Suppose I try CountA($A$4:$A$410) function on the data (as shown in the second image), it would return 13. Because in the entire range (A4:A410) there are only 13 non blank cells. Rest are Blank. Width would be one to ensure that we are talking about a single column(or we can say series or x series values). See the following screenshot :



Similarly, I would create a SharePriceRange



After creating the dynamic named ranges, I will need to assign the name ranges to our chart. In our current chart, we have one series, and one x Series. That is why I have only used two dynamic named ranges. If we will have more series, then we will need to add as many named ranges equal to the number of series present in the chart.

For assigning a named range, I will need to edit the source data. Following is the screenshot of two buttons which we may need to click in order to edit the appropriate series.



The sheet which I am operating is Sheet2, so I will need to ensure that I have included the worksheet name along with the name range. See the two screenshots :








I have created a sample spreadsheet to demonstrate it properly. To download the spreadsheet, please Click here.

Thanks,
Vikas Bhandari