Following on from Creating Named Ranges in Excel®, this blog post describes how to create *dynamic* named ranges.

If you’re not familar with named ranges, I’d really suggest google searching “named ranges in excel” or read the post linked to above.

As mentioned many times on this site, named ranges are really useful, however there are situations where the data you need to reference is not in a static cell range – that is, the cell range is dynamic. To combat this, we can use the OFFSET and COUNTA functions.

First, let’s look at COUNTA – it is a function that most Excel users would be familiar with. The COUNT function returns the number of numbers in a cell range. For instance:

=COUNT($A$1:$A$10) would equal 10

=COUNTA($A$1:$A$10) would equal 10

=COUNT($B$1:$B$10) would equal 0

=COUNTA($B$1:$B$10) would equal 10

Basically, COUNTA counts the number of cells in a range that have at least some value – not just numbers.

=COUNTA($B$1:$B$10) would now equal 5

It is important to understand that this as it will be instrumental in determining the height and width of our dynamic named range.

The next function to investigate is the OFFSET function. The definition of OFFSET is:

=OFFSET(reference,rows,cols,[height],[width])

For those of you who don’t know, the square brackets (i.e. “[" and "]“) surrounding the parameters of a function indicate that it is optional. This is fairly standard coding convention.

To break this down:

- reference corresponds to the starting cell position
- row corresponds to the number of rows we are going to move downwards, relative to the starting cell (or upwards, if we use a negative value.)
- column corresponds to the number of columns we are going to move to the right, relative to the starting cell (or to the left, if we use a negative value.)
- [height] corresponds to the height (or number of rows) of the range starting at the adjusted position.
- [width] corresponds to the width (or number of columns) of the range starting at the adjusted position.

An example will almost certainly make this easier…

We previously defined the three (3) named ranges, “Origins”, “Destinations” and “Distances” defined thusly:

But by using OFFSET and COUNTA we can update our definitions to by dynamic:

- For
**Origins**, =$A$2:$A$4 becomes =OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

We start at cell $A$1, we move down 1 cell, across 0 cells to reach our starting position (i.e. $A$2 – you could just as easily have had $A$2,0,0 as your first three (3) parameters.

Now, this is the trickiest (not, tricky, but trickiest) part. We specify a height of COUNTA($A:$A)-1. What does this mean? Well, there are four (4) cells with values in all of Column A ($A:$A) in this example, but we don’t want to include the heading as it doesn’t constitute an “Origin”. We simply subtract one (1) to ignore it and what we have left over is the height. The final parameter value of 1 simply indicates that the dynamic named range of “Origins” is one (1) column wide.

- For
**Destinations**, =$B$1:$D$! the same premise holds. This becomes =OFFSET($A$1,0,1,1,COUNTA($1:$1)-1)

We start at cell $A$1, we move down 0 cells, across 1 cell to reach our starting position (i.e. $B$1 – you could just as easily have had $B$1,0,0 as your first three (3) parameters.

Now the (not so tricky anymore) tricky part; we specify a height of 1 and a width of COUNTA($1:$1)-1. This simply counts up all the values in Row 1, subtracts to row heading in $A$1 and sets the dynamic named range of “Destinations”.

Now that we’ve done these, we can do something particularly fancy to define the “Distances”. This may be a slight point of difference to other tutorials on this topic. The same follows for the other named ranges.

- For
**Distances**, =$B$2:$D$4 becomes =OFFSET($A$1,1,1,COUNTA(Origins),COUNTA(Destinations))

How cool is that! Straight away you may have noticed something special about this definition, but we’ll go through it parameter by parameter, just for completeness (because that’s the kind of guy I am).

We start at cell $A$1, we move down 1 cell, across 1 cell to reach our starting position (i.e. $B$2 – you could just as easily have had $B$2,0,0 as your first three (3) parameters.

Now the (not so tricky anymore) tricky part; we specify a height of COUNTA(Origins) and a width of COUNTA(Destinations). We had already done the hard work defining our row and column “headings” that we can just count them up! We are using dynamic named ranges in another of our dynamic named ranges.

I must admit, I used to use the formula:

=OFFSET($A$1,1,1,COUNTA($A:$A)-1,COUNTA($1:$1)-1)

Now, while that also works, it isn’t as consise and doesn’t demonstrate what we are trying to define with our named range.

With these definitions, adding more rows and columns will simply increase the COUNTA($A:$A) and COUNTA($1:$1) counts, thus dynamically expanding the range defined using the OFFSET function.

There are certainly aspects of the functions that I have omitted. I’d welcome any comments or criticism, so please feel free to add any comments. If you require further clarification on any part of this article, please leave a comment, or you can email me at: ryan@kirgs.com

*Ryan Kirgan is from Sydney, Australia.*

## { 12 } Comments

That’s a very good tip. Thank you for sharing this to us. Until now, there are still many who aren’t familiar using excel to make their work more efficient.

Your blog tips on OFFSET & COUNTA helped me a great deal. I have a problem that I think I figured out, but would like to hear if you have an explanation about why it works or if there’s a better way to do it. For instance:

Column A:

Title

Date

Mar 11

Dec 10

Sep 10

Jun 10

Mar 10

Dec 09

Sep 09

Jun 09

Mar 09

Column B:

Name

Figure

10

15

20

25

30

35

40

45

50

As you can see, I’m adding the most recent figures by inserting a row above row 3. For my chart, I need only the most recent eight figures to appear. For now, this Name seems to work:

=OFFSET(GeneralData!$B$2,COUNT(GeneralData!$B$3:$B$11)-8,0,8)

When I insert a row above 3, this is my new Name, but it still updates the chart correctly. Can you explain why?

=OFFSET(GeneralData!$B$2,COUNT(GeneralData!$B$4:$B$12)-8,0,8)

I’m absolutely new at this. Thanks for any help you can offer.

@Jen

The general premise is ok, but I think that the parameters may not necessarily be in the correct order. Are you trying to SUM the 8 most recent values in the column?

If that understanding is correct, the formula you have listed will only list the most recent value. Breaking down the OFFSET formula, you have:

`=OFFSET(reference, rows, cols, [height], [width])`

Having

`$B$2`

, this means your starting point is the cell that has “Figure” (good place to start).The next parameter tells OFFSET how many rows to move to reach the beginning of what you want the range to be. I’d make this simply

1as you are always going to want the cell just beneath the heading to make a start.You don’t want to move across any columns, so the next cell value will simply be “0”.

You want the range to be 8 cells high to capture the 8 most recent numbers, so the 4th parameter (for “height”) will be “8”.

The final parameter, for the width of the range, will be 1 (as the range is only 1 cell wide.)

Therefore,

`=OFFSET($B$2,1,0,8,1)`

will define your range. Assuming you want the sum of these 8 values, you need to enter:`=SUM(OFFSET($B$2,1,0,8,1))`

Hope this helps (sorry if I’ve misunderstood what you were after.)

P.S – The reason the formula value didn’t change was because when you inserted the row, the cell references updated (

`$B$3:$B$11`

to`$B$4:$B$12`

, the count of both will always equal 9 (provided there are values in there – therefore, your value for the second parameter – # of rows to move – was always 9 – 8 = 1!))@Ryan – I was hoping you’d write back! Sorry I wasn’t more clear. My chart is creating 8 bars of data with Date on the X axis and Figure on the Y axis. The 8 bars feed from rows 3-10 of columns A and B. So, imagine a bar graph with the first bar:

Mar 11 and 10 units; second bar: Dec 10 and 15 units; …all the way to the eighth bar (and 10th row of data): Jun 09, 45 units.

As I update data, I insert a row above row 3. So, I still need rows 3-10 to feed the chart, but now the chart would go from Jun 11, Mar 11,…Sep 10 — and the data from Jun 09 would no longer be on the chart.

Thanks for your expertise! All the best from Denver, CO, USA!

@Ryan – =OFFSET($B$2,1,0,8,1) works perfectly for my needs. Thanks, again!

@Jen

I’m glad to here you had some success. An interesting thing to note is that if you do use a named range for the source data for the chart, you need to reference it by including the worksheet name as well as the named range name. For example, if the named range was “dates”, you’d need to reference this as “Sheet1!dates” if that is where the data resides.

I’ve attached the example from your earlier email here: Chart Example using OFFSET for Jen.xls.

Hope this helps.

@Ryan

You’re pretty awesome…thanks for the example!

Why not just use a table that expands it’s range automatically? Or if a named range is needed – name the range, then make it a table.

This seems much simpler than entering all these formulas.

i entered “name a range” value for (A4,B4) cell in excel,to read cell value we use sh.cell(row,col) but how to get name range value for that cell.

Polly,

Have you tried:

`Dim NRValue`

NRValue = ThisWorkbook.Names("<

named range>").RefersToRange.ValueMsgBox (NRValue(1, 1))

This should work for you if your range refers to a single cell. Otherwise, it is pretty easy to modify for larger ranges.

Hi Ryan,

This explanation help me a lot. I already defined the name and it updates the values with no problem. How would I keep it alphabetically sorted?

=OFFSET(DropdownData!$C$3,0,0,COUNTA(DropdownData!$C:$C)-1,1)

Hi Alex,

I couldn’t think of a really elegant way to do this without using VBA, but should the named range contain only numbers, you could try the following:

`={INDEX(testRange,MATCH(LARGE(testRange+ROW(testRange)/10000,ROW($C1)-ROW($C$1)+1),testRange+ROW(testRange)/10000,0),1)}`

It’s an array formula, so you need to press CTRL + SHIFT + ENTER when entering the formula. You then simply drag the formula down until it is the same size as your unsorted list.

Let me know if you are looking to sort text, as this will require a different solution that I can’t think of off the top of my head!

## { 1 } Trackback

[...] Once you’ve mastered named ranges, you’ll find that there are many more application than what have been listed here. You might also find that there are limitations, least not, that you have to keep redefining your range every time you add more data (excluding inserting rows/columns in the middle of the range as it expands the cell range reference automatically.) If this is the case, you’re now ready for Dynamic Named Ranges: Creating Dynamic Named Ranges in Excel® using OFFSET & COUNTA [...]

## Post a Comment