Dynamic Named Ranges in Excel
Range Names are widely used by people familiar with Excel as a means of making certain things more familiar and understandable particularly when you go back to something you haven’t looked at for some time. Annual Pay Review is perhaps a good example because as it’s name implies we are going to be looking at this annually and not every week, so we have time to forget what we did.
The problem with named ranges is that they are normally fixed. Consider the following;
|
|
A |
|
1 |
Band |
|
2 |
M |
|
3 |
1 |
|
4 |
2 |
|
5 |
3 |
|
6 |
4 |
If we create a range name for “BAND” to refer to the data for a lookup then we will have the formula “=$A$2:$A$6”. It’s important to notice that a range name is only a formula that points to a range of cells. Depending on an individuals experience with other spreadsheet products one may fall into the trap of believing that a ‘named range’ is something very special and that may well be the case in some other Spreadsheet packages. However, we are dealing with Excel and as far as that goes a ‘Range Name’ simply refers to a formula and nothing more.
Now the problem with our example is that it is ‘static’. If next year we add a further band to our list, unless we alter the formula the range name will still refer to the cells A2:A6. In order to get round this we use Dynamic Range Naming. This gives us a self adjusting formula so that when we add an additional band to the bottom of our list the range adjusts without us doing anything else.
To do this we are going to use the OFFSET Worksheet function
=OFFSET(reference, rows, columns, height, width)
and create the following
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Simple isn’t it? No.
As you can see the OFFSET function has five parameters.
The first one ‘reference’ is our anchor point. This is usually either row 1 or row 2 in our chosen column. The only reason we use row 2 as the start cell for our range is that we might want to put it’s name in row 1, otherwise we would use row 1.
The second parameter ‘rows’ is an offset number of rows from our anchor point. That might seem unnecessary since if we’ve decided what our ‘anchor’ cell is why would we want to offset from there. We normally set this to 0 because we don’t want to offset from our anchor but there are rare instances when using dynamic ranges with chart series where you might want a moving offset moving over time.
The third parameter is ‘columns’ and works in precisely the same way as the rows parameter so is normally set to 0.
The fourth parameter ‘height’ is the ’dynamic’ part of our equation. This gives us the number of rows in our range and here we are going to use the COUNTA worksheet function. This function counts non blank cells in the range we give it. So if we code ‘COUNTA($A:$A)’ then the answer we will get is the number of non blank cells in column “A”. You will notice in our example that we have set ours to ‘COUNTA($A:$A)-1’. This is because we are using cell $A$2 as our anchor point and so we want to exclude that from the number of non blank cells in column A.
The fifth parameter is ‘width’ and can work in the same way as the ‘height’ parameter. However, we tend only to use this on Lookup tables and since we using a simple list we set this to ‘1’. If we were using a lookup table with several columns then we would set this to the number of columns in our table. In rare circumstances it is possible to use the same ‘dynamic’ approach that we use for height.
Using this technique when we add another Band to our list our named range will automatically adjust because the COUNTA function will alter the number of non blank cells in the column to include the new band we have just added.
N.B. Using dynamic named ranges in this way means that you cannot have any non blank cells in the column below your named data. For this reason we tend to store these ranges on a separate sheet for our normal data.
Now you understand that let’s take it a step further. You can use named ranges as the source and axis ranges for a chart. We all know it can be fun playing with charts and there are people gainfully employed updating the same charts ever Monday with the latest set of new data and using the ‘Add Data’ function to extend the series and axis of a given chart. Some people even ‘Add’ lasts weeks/months data and remove the oldest item so that have the appearance of a constantly rolling graph.
It is possible using a
|
|
A |
B |
|
1 |
Dates |
Data Series |
|
2 |
01/01/04 |
41 |
|
3 |
01/02/04 |
71 |
|
4 |
01/03/04 |
5 |
|
5 |
01/04/04 |
90 |
|
6 |
01/05/04 |
54 |
|
7 |
01/06/04 |
11 |
|
8 |
01/07/04 |
21 |
|
9 |
01/08/04 |
25 |
|
10 |
01/09/04 |
12 |
|
11 |
01/10/04 |
62 |
|
12 |
01/11/04 |
58 |
|
13 |
01/12/04 |
86 |
|
14 |
01/01/05 |
25 |
|
15 |
01/02/05 |
15 |
|
16 |
01/03/05 |
96 |
|
17 |
01/04/05 |
45 |
|
18 |
01/05/05 |
8 |
We create a range called ‘Dates’ using the formula;
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
That gives us all the dates from 1/1/04 to 01/5/05. Suppose we want to just select the last 12 months as at the current date. What we need to do is make the ‘anchor’ cell a date that is 12 Months before the current date. Lets assume that the current date is 27/4/05. If we use the formula:
="A"&MATCH(NOW(),Dates,1)-11
We will get the answer A5. If we then incorporate this in
our Dynamic Range Formula to give us the address of our ‘anchor’
cell and ‘hard code’ the number of cells we want the range to
extend for we will end up with a range that covers the previous 12 months. e.g.
=OFFSET(INDIRECT("A"&MATCH(NOW(),Dates,1)-11),0,0,12,1)
Notice that we have to use the INDIRECT function to indicate that we are referring to a cell address. All we need now is a similar formula to name our data series. To do this we use more or less the same equation but use the 3rd parameter of the OFFSET function to offset the from the Dates series. So we create a range called ‘Sdata’ using the following formula:
=OFFSET(INDIRECT("A"&MATCH(NOW(),Dates,1)-11),0,1,12,1)