Skip to content

Creating Dynamic Named Ranges in Excel® using OFFSET & COUNTA

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:

1 to 10, a to j

1 to 10, a to j

=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.

1 to 10, a to j

1 to 10, a to j (alternate)

=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…

Origins » Destinations » Distances

Origins » Destinations » Distances

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

Define Name » Origins

Define Name » Origins


Define Name » Destinations

Define Name » Destinations


Define Name » Distances

Define Name » Distances

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.

Ryan Kirgan

Ryan Kirgan

Share

{ 12 } Comments

  1. Investments Vietnam | July 15, 2010 at 6:54 pm | Permalink

    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.

  2. Jen | January 27, 2011 at 9:28 am | Permalink

    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.

  3. Ryan Kirgan | January 27, 2011 at 5:43 pm | Permalink

    @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 1 as 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!))

  4. Jen | January 28, 2011 at 5:51 am | Permalink

    @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!

  5. Jen | January 28, 2011 at 11:07 am | Permalink

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

  6. Ryan Kirgan | February 2, 2011 at 11:46 am | Permalink

    @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.

  7. Jen | February 18, 2011 at 11:20 am | Permalink

    @Ryan
    You’re pretty awesome…thanks for the example!

  8. Dorothy | May 26, 2012 at 6:15 am | Permalink

    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.

  9. Polly Fuentes | January 18, 2013 at 7:35 pm | Permalink

    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.

  10. Ryan Kirgan | January 21, 2013 at 8:30 am | Permalink

    Polly,

    Have you tried:

    Dim NRValue
    NRValue = ThisWorkbook.Names("<named range>").RefersToRange.Value
    MsgBox (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.

  11. Alex | January 24, 2013 at 7:55 am | Permalink

    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)

  12. Ryan Kirgan | January 25, 2013 at 10:32 am | Permalink

    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

  1. [...] 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

Your email is never published nor shared. Required fields are marked *
*