Dynamic Excel Named List Grows Automatically

You can quickly create a named range in Excel, but it doesn’t automatically expand to include new items that are added at the end of the list.

namedynamic01

If you plan to add new items to a list, create a dynamic named range, by using an OFFSET formula. In this example, the formula entered in the Refers to box is:

=OFFSET(Prices!$B$1,1,0,COUNTA(Prices!$B:$B)-1,2)

  • Using cell B1 as the reference cell, the list will start 1 row down, and 0 columns to the right.
  • It counts all the items entered in column B, and subtracts 1, because the list won’t include the heading in B1.
  • The list will be 2 columns wide.

namedynamic02

Then, as new items are added to the list, the dynamic named range will automatically expand to include them.

Watch the Video

To see the steps for creating a quick named range and a dynamic named ranged, you can watch this video tutorial.

_______________

You may also like...

39 Responses

  1. Stan says:

    Hi Debra:
    I like the idea of using a Dynamic Named Range, however, for the workbook that I am using now, this range is in the middle of the sheet, with data above it.
    Is there a way to handle a situation like this.
    Thanks – I appreciate your insights into Excel, I use your tips all of the time.

  2. Hi Stan, if you know which row the range starts in, you could subtract everything above that. For example, if the range starts in B31, and that position won’t change:

    =OFFSET(Prices!$B$1,30,0,COUNTA(Prices!$B:$B)- COUNTA(Prices!$B1:$B30),2)

    Or, name the start cell for the range, and use that to find and count the cells above it:

    =OFFSET(Prices!$B$1,ROW(StartCell)-1,0,COUNTA(Prices!$B:$B) – COUNTA(OFFSET(Prices!B1,0,0,ROW(StartCell)-1,1)),2)

    • Dave says:

      Thank you so much for this answer. I searched high and low for 30 minutes. Thanks!

      • Dan says:

        Following on from this, when I use a dynamic range to pick information from the middle of a table of data I get no column headers when I reference the named range in a Pivot Table – I’ve tried selecting the first row and the named range as a source for the Pivot and even using both the first row and the dynamic formula as a named range but neither work.

        I’ve been searching for days and can’t find any information on how to do this.

        Please help! :)

        • @Dan, you won’t be able to create a pivot table from data in the middle of a range. Perhaps you could use an Advanced Filter to extract the data to a different worksheet, and base the pivot table on the extracted data.

  3. Jayson says:

    I love these and use them a lot, but recently I have been rethinking how much I use them. Typically I use these together with a list via data validation so that the drop-down list updates appropriately. But then I found out that using a dynamic range doesn’t restrict user entry in the cell. If you have a hard coded range, then you can set up data validation to only accept items in that list. If you try to type anything else, it will throw an error and tell you to fix it. With dynamic ranges, you can type whatever you want in the data validation cell, even if it doesn’t match the list, and you will get no error.

    Annoying.

  4. Jayson, the data validation lists based on a dynamic range should block any invalid entries, unless there’s a blank cell in the dynamic range. Check your formula to make sure you aren’t including an extra row.

  5. Jayson says:

    Debra- That was my thought at first. Would you care to try and let me know what you find? I’ve just tried again tonight. I don’t see a blank row (no blanks in the data validation drop-down).

  6. Jayson, can you send me your file or upload it somewhere that I can access it?
    ddalgleish AT contextures.com

  7. Martin says:

    Debra,
    I totally agree with your post. Since I first know of this thanks to Chandoo’s post, I have never stopped using it.

    Stan, maybe you’d like to consider split the contents of your sheet in 2 different ones: one with the data and one with the summary or the remaining contents. It helped me on structuring both macros and formulas, and it’s easier to understand and maintain by someone else if the chance arises, proving your professionalism.

    Rgds,

    Martin.

  8. Doug Glancy says:

    Debra, in my brilliant comment ;) at the link below, I added a technique to deal with an empty list – a list with just a header: http://www.dailydoseofexcel.com/archives/2010/01/07/new-years-resolution-no-more-offset/#comment-43246.

  9. Martin, you’re right, separate sheets certainly make things easier to maintain.

    Doug, thanks for the link! That’s a nice solution to the problem of deleted heading rows.

  10. Boon says:

    One problem I have with dynamic name lst is when I create a pivot table using query (to combine various sheets), these names does not seem to be recognised by MS Query.

  11. wayne says:

    HI Debra, I need to do the same thing but instead of adding rows to columns, I need to dynamically add columns to a single row. For example, column A starting in cell 1 has 25 rows of time intervals. Each day I need to add the previous days numbers to the next empty column. I have that figured out, but I would like to use a named range for each of the 25 rows and have them dynamically expand as I would like to use the named ranges in reports I have created based on this data. So each row must have it’s own unique named range that expands across columns. Can you use a variation of the code above to accomplish this? if not, can you shed some light on how it might be done. Love your site, thanks

  12. @Boon, you might have to use a non-dynamic named range, and use a macro to redefine the range after you’ve added or deleted data.

    @wayne, you can use a COUNTA function to calculate the number of columns too. For example, count the cells with data in row 1:

    =OFFSET(Prices!$B$1,1,0,COUNTA(Prices!$B:$B)-1,COUNTA(Prices!$1:$1))

  13. Doug Glancy says:

    Does anybody use Data>Lists (or Tables in 2007) for dynamic data validation lists? I’m just fooling around with it. After creating the List, I created a named range, using only the column of the list that I want in the data validation, and excluding the header.

    This seems to work well, with the following benefits:
    – Can have more than one list on a Validation Lists worksheet (each one having its header in Row A) and am only allowed to insert/delete rows from one List at a time. (With more than one dynamic range on a sheet it’s easy to accidentally delete a whole row spanning multiple ranges.)
    – Am prevented from deleting a list’s header row.
    – The named range expands and contracts with the list as I add or delete rows, just like a dynamic range.
    – The data validation range isn’t affected if you add other items below the List in the same column.
    – Deleting all the items in the list results in a blank as the only data validation choice – the named range adjusts to include the “*” row of the List. However, when I add items back in the List, the “*” row is no longer included, and the blank Data Validation choice goes away.
    – the named range is visible in the Name box

    I’d be interested to know if people use this method. I don’t recall seeing it mentioned.

  14. Doug, the List and Table features certainly have many benefits, as you mentioned. However, many of my clients still have some people using Excel 2002 or earlier, so I can’t risk using those features, since they aren’t supported.

  15. Doug Glancy says:

    Thanks Debra. After I posted my comment I realized that I’m only working with 2003 or later right now, but that the issue is exactly what you said for earlier versions. Other than that, Lists/Tables seem to work very well in place of dynamic ranges for DV. I’ve written some VBA code to convert a range to a List and then add a named range to the first column for this purpose.

  16. Aviva says:

    Hi.
    Thanks for the great tip.

    I’m trying to implement dynamic lists (on a third dependent list http://www.contextures.com/xldataval02.html#Third) using Excel 2003 and it doesn’t seem to be working. Does Excel 2003 allow for dynamic named lists?

    Thanks,
    Aviva

  17. Thanks Aviva, and to use a dynamic list in dependent data validation, you can follow the instructions here:
    http://www.contextures.com/xldataval02.html#Dynamic

  18. Johannes says:

    Hey Debra

    Great post, I have a question though and at the risk of being asking one too complicated for a comment…..

    What I would like to do is have primary and dependent lists that update automatically. The primary list is a list of phases, the dependent list is a list of sub phases. The user chooses a phase in column A, which then dictates which list they will choose from in column B. I want users to be able to add/edit/delete both phases and sub phases as they see fit since this is a living document. They way I am doing it now is through a recorded macro that uses insert>name>create, but it adds all the empty cells to the list.

    Thanks for any help
    Johannes

  19. joju says:

    Dear Debra,
    My name is joju andi am working in a construction company i would like to learn step by step for applying dinamic name range i have some datas of employees like

    Code no Name Designation
    2343 Mr.A Construction Manager
    2345 Mr. B Project Engineer

    Like this the data base will be added with new code no and employees in thease cases how can i use dinamic name range

  20. Steve says:

    Hello, we use excel to record employee time worked. When a employee works over 40 hours in a given week, we require a formula that will distinguish when the total hours total over 40 and place that (additional over 40) value into a different cell which will then calculate time and a half of pay for each hour of over time.

  21. Sherry says:

    Dear Debra—

    First of all, I love dynamic ranges, and have used them in the past, but this time is different, and I have no clue how to approach this “challenge”. I am pulling data from Cognos and linking it within Excel to perform various calculations. Now the report I have has a total of 105 rows of data and 19 columns. These 105 rows of data include “header” rows to seperate the data by location and then by person. I need to be able to link data based on both the location and then the person. A dynamic range does not appear to be something that would work because the way my data is. The number of rows would vary earch time the report is run, but the number of columns will remain consistant. Any ideas Since there is no “dead space” between each location’s data?

    I can probably recreat my Cognos report to list each location on a seperate page (which would be a seperate tab within Excel), but would I need to set up a range for each column (remember there are 19) within each location (there are about 12)? Any assistance you can offer would be greatly appreciated!

    • Gene says:

      Sherry,
      You may still be able to use dynamic ranges. First, create a dynamic range for the whole column with Location names so you can search for Locations in that range. Assuming you have a fixed set of Location names, you could use the MATCH function to find the starting row for each Location to determine the row offset, then you could use MATCH to find the top row again and subtract it from the start row of the next Location (using MATCH for the 3rd time) to get the height of the range. Depending on how you define the start row you’ll probably need to subtract 1 more from the range height. You’ll also need to add a dummy Location at the bottom of the whole dataset to be able to find the bottom of the last Location using the same scheme. It’ll be a messy formula, but you only have to do the setup once! Remember, you can use wildcard characters in MATCH to shorten the formula.

  22. Michael says:

    Dear Debra

    I have a pivot table reporting data for various segments of sales with a subtotal for each segment such as the segments below, i need to report on each segment and rather than having one pivot table per segment, how can i use a dynamic named range to extract one segment such as “General Sales”.
    Contract Sales
    Contract Sales Total
    General Sales
    General Sales Total
    Growth and Replacement
    Growth and Replacement Total
    Other Sales
    Other Sales Total

  23. Paula says:

    The method I use seems a lot easier:
    Create the list, use a header.
    Highlight and name the range without header)
    Highlight the header and range
    CTRL L to create table, my table has headers.
    This way new rows added to the table are also added to the range.

  24. shammi says:

    Hello,
    I need some help in excel.

    I need to display the Date as July 29,2013 in excle in single row and these should be change manually and another i have to dspaly time through list for a specific time like 4:02,16:30,17:05 the time doesnot change once saved.

    Please send me the excel example if this possible on my mail id shammi.adlakha@gmail.com

  25. 6tel says:

    Hi Debra.

    I’m having a column in a spreadsheet which I’d like to convert to a dynamic range. And I’m using your formula, which seems pretty logic to me… Nevertheless, this column has 68 rows, and there are only 13 rows of cells with text data: the rest of the cell rows are empty… When I check into to the dynamic range I created following your procedure and formula, it seems the formula is working but it only takes the range of the first 6 cell rows of empty data and a just a cell row with text. The range does not go down any further to my 68th row and leaves 62 rows not being wrapped by the dynamic range I just set. Would you have an idea on what am I doing wrong?

  26. 6tel says:

    Hi Debra. Sorry once again.

    If you could just post an article or explanation on dynamic named ranges with blanks in a column (with helper columns) I would be much grateful.

    Thanks for your blog.

  27. 6tel says:

    It seems this is the formula when dealing with blanks among data in cells, given to me by Debraj Roy at the Chandoo forums… It is a very useful formula (seems like a pretty elegant solution), but I do not understand how it works… If someone could explain it to me, he/she’d be doing a great favor to the world of non-expert Excel users.

    =SpreadsheetX!$O$6:INDEX(SpreadsheetX!$O:$O;MATCH(REPT(“z”;255);SpreadsheetX!$O:$O))

    Hope it helps. Regards.

  28. Duncan says:

    6tel – An easy solution is to insert a dummy column that always has a value in it (for example put the letter A in column 1 of every row). The COUNTA part of the OFFSET formula does not have to refer to the column that the named range is being assigned to (all you are trying to do is count the number of used rows in the sheet), so you can then use COUNTA($A:$A) to determine dynamically the number of rows in the range.

  29. Katerina says:

    Dear Debra,
    thank you very much for all your help. I was wondering: dynamic ranges are very useful for lists of data that are only growing in time, but what about a list that can be either longer or shorter?

    We are reporting on the number of tickets we get per region and of course, there do not need to be all regions at all times at the report – only the ones that sent us something to solve :) Is there any solution for that as well?

    Thanks again,

    Katerina

  30. GJ Silver says:

    Hi Debra,

    Thanks for all your insight, it has helped me a ton. Here is my challenge:
    I have many dynamic named ranges in a sheet; call each one a section. Each section/range is over columns A thru E. I’ve created a check box for each section/range, and if the check box is checked it shows the section/range, and unchecked Hides the range.
    Using the offset and counta I can define each section as a range.
    The problem is if I insert a row into any section/range, it pushes the last row of the section/range out of the section/range…
    So what I need to do is define the start and end of each section/range, and allow that section/range to grow from the middle.
    Both of these 2 lines (2 different options) will define my 1st section/range; but they don’t grow from the middle:
    =OFFSET(‘RRC 2’!$A$4,1,0,COUNTA(‘RRC 2’!$A$4:$A$16)-1)
    =OFFSET(‘RRC 2’!$A$4,1,0,COUNTA(‘RRC 2’!$A:$A)-41)

    Please advise.
    Thank YOU!

    GJ

    • GJ Silver says:

      Hi Debra,

      I just realized that each section has blank and populated cells, and if that number changes (a current blank cell gets data), then the range is thrown off as well.
      So I am now more confused…
      Is there a way to define the dynamic range that grows from the middle regardless if a row/column has data or not?

      Thanks Again,

      GJ

  31. Chaman says:

    Is it possible to have the range of columns to be updated automatically as well, instead of fixed number (2 in this case)?

  32. Nate says:

    This is not working for one sheet and is working for another. On the sheet it doesn’t work the print range is based on the equation (correctly), but the range is not dynamic and gets converted to a strictly set notation (precisely, =OFFSET(PKG_num_Pass,-1,0,MAX(OFFSET(PKG_num_Pass,1,0,500,1))+4,16) is converted to =’Passivation Log’!$C$2:$R$7). Does anyone know why this may be happening?

  1. October 8, 2012

    […] […]

Leave a Reply to 6tel Cancel reply

Your email address will not be published. Required fields are marked *